If you’ve been playing with Entity Framework and used the wizard to create a model from an existing database, you’ll see that the resulting model  looks something like this which is a small portion of the EDM built from Northwind.

 

 

Compare the Orders Entity above to the Order table schema

 



 

and you’ll quickly notice that the foreign keys for CustomerID, EmployeeID and ShipVia are "missing".

 

We are used to using foreign keys as a means for defining relationships between tables and realizing them through the joins when we query. In our objects, we use the foreign keys as a means of finding our ways back to another object.

 

In the Entity Framework we have a different method of finding our way from one table to another – Associations & Navigations (which come as a team).

 

The model is more "holistic" than what we are used to.

 

The fact that there is a relationship between Orders & Customers is identified by an association (which in the model is represented by the lines between the entities). The Association is named "FK_Orders_Customers" and describes that in this relationship there will be 0 or 1 customers and many orders. (Though I can’t figure out how any order could be without a customer…)

 

So far this is only a part of the puzzle. Still nothing about CustomerID in there.

 

Then there are the Navigation properties. If you are working with a Customer entity, it uses it’s Orders navigation property to know that it has a relationship with orders and that relationship is defined by the association.

 

Okay so stop there a minute! Why have the Navigation property if we already have the Assocation?

The association is not part of the customer (nor part of the order). It floats outside of those. So when you are wokring with a customer, you need one of the properties to give you access to the orders, therefore a navigation property. The association is shared because the Orders entity has a property that allows you to navigate over to the customer when you are working with the objects.

 

When you create an Order object, while you don’t have Order.CustomerID, you do have Order.Customers, the Navigation property and that gets you to customers without having to say "where order.customerid=customer.customerid"

 

(From o In nw.Orders Where o.OrderID = 10281 Select o.Customers).First

 

This query will bring back  the customer for the specified order.

 

If you looked at the xml which defines the Associaitons and the Navigation proeprties in the conceptual model, you will see that there are still no references to customerid. So how does it work?

 

The fact that it is the customerid that is the foreignkey is defined in the Storage model. The Order entity in the storage model has a customerid and the FK_Orders_Customers association in the storage model has a reference to the CustomerID in it’s ReferentialContraint element.

 

Finally in the mappings, there is an AssociationSetMapping that very clearly defines that CustomerID is the means for relating Orders and Customers.

 

When the data is surfaced in the Conceptual model, all of the relationships have been sorted out and there is no longer (for most intents & purposes) a need to have the customerid in the order entity. We shouldn’t need to use that to navigate between orders and customers.

 

Some people beg to differ on that matter.  See this forum thread [http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1663304&SiteID=1] and also note that in response to a list of Entity Framwork suggestions by Roger Jennings , Mike Pizzo replies to suggestion #9.

 

9. Provide read-only access to foreign key values.

This is actually a feature I’m fighting to get into our final milestone for V1. Can you describe the scenarios where this is used? Do you need the ability to query on the foreign key value, or simply expose it on the domain object?

 

There’s one other thing that needs pointing out here. OrderDetails has an OrderID and a ProductID. So am I full of it? Well, that could be a different topic for debate, but in this case OrderID and ProductID are not only Foreign Keys but they are both Primary Keys in the Order Detail table as well. They become EntityKeys in the OrderDetails entity. Note the primary key icon for the two fields.

When you build an EDM from an existing database in Entity Framework, you can get Views & Stored Procedures in addition to tables. You’ll find that if you select Stored procedures, you will also get your functions.

The stored procedures are only defined in the storage layer. The return type is not determined therefore there is no Entity created or the mapping that would be between the two.

The Entity Framework documentaiton describes how to build Insert, Update and Deleted sprocs but not how to build sprocs that return data.

I got some help on the forums which showed building a stored proc in the EDM from scratch, but I wanted to take one that had been put into the model and complete it. I’ve written a short article that walks you through step by step that will go up on www.DevSource.com shortly, but in the meantime, I wanted to show how I finished up the CustOrderHist procedure. The article goes into more detail and explains the what’s and why’s. I will put a link to it in this post when it goes online.

What you will see created by the wizard is this function in the storage model section of the EDMX.

<Function Name=”CustOrderHist” Aggregate=”false” BuiltIn=”false” NiladicFunction=”false” IsComposable=”false” ParameterTypeSemantics=”AllowImplicitConversion” Schema=”dbo”>
<Parameter Name=”CustomerID” Type=”nchar” Mode=”in” />
 </Function>

You’ll need to create an entity, entityset and FunctionImport in the conceptual model. In the storage model, you’ll need a matching entity and entity set and then you need to map the FunctionImport of the Conceptual model to teh Function in the storage as well as map the entitysets.

Here are what the rest of the code chunks look like.

I created the Entity using the designer and that automatically created the EntitySet.

<EntityType Name=”CustOrderHistResult” >
  <Key >
      <PropertyRef Name=”ProductName” />
  </Key>
   <Property Name=”ProductName” Type=”String” Nullable=”false” MaxLength=”40″ />
   <Property Name=”Total” Type=”Int32″ Nullable=”false”/>
</EntityType>

<EntitySet Name=”CustOrderHistResultSet” EntityType=”NWModel.CustOrderHistResult” />

The FunctionImport in the Conceptual layer which has pointers to the entity and entity set.

In the storage layer, add the matching entity and entity set; they look like this. Remember to put them in the correct sections!

<EntityType Name=”CustOrderHistResult” >
  <Key >
      <PropertyRef Name=”ProductName” />
  </Key>
   <Property Name=”ProductName” Type=”nvarchar” Nullable=”false” MaxLength=”40″ />
   <Property Name=”Total” Type=”int” Nullable=”false”/>
</EntityType>

<EntitySet Name=”CustOrderHistResultSet” EntityType=”NWModel.Store.CustOrderHistResult”/>

And lastly the mappings:

The first one I did manually.

<FunctionImportMapping FunctionImportName=”CustOrderHist”       FunctionName=”NWModel.Store.CustOrderHist”/>

The second I actually created by using the designer. Here is the xml that was generated

With all of this in place, I can now use or query the stored procs as I would any of the other entities in my model

Dim query = From CustOrders In nw.CustOrderHist(“ROMEY”) _
    Where CustOrders.Total > 10 Select CustOrders Order By CustOrders.ProductName

[Update at bottom of post]

I wrote this in response to a question on the ADO.NET Orcas forums and thought I would copy it here.

Note that Danny Simmons writes more information about Entity Framework and DataTables in the thread.

Here’s my solution for now…

LINQ to Datasets does allow for conversions from queries to Datatables, but it doesn’t seem to be able to deal with ObjectQueries. I then queried my objectquery to return an IQueryable and also an IEnumerable, but I couldn’t get the datatable conversion to work. Look for CopyToDataTable in the VS2008 documentation for more info on that.

So since I couldn’t get at it I rolled my own using Mike Taulty’s previous example (not for ObjectQueries though) as a start.

This example uses the ObjectStateManager so I can dynamically get at the values. As long as I’m already using the ObjetStateManager, I also use it to get some metadata, even though I could have used the MetadataWorkspace for that.

Assuming we are starting with nwrows which is an ObjectQuery for a simple Linq to Entities query…

While Jim Wooley is looking at performance in LINQ to SQL, I’ve finally pulled the VS2008 installation on my VPC out of the mysteries of SQL Server Express and installed SQL Server Developer Edition so that I can look at my queries as they come into profiler.

Note that this is all based on VS2008 Beta2 and Entity Framework Beta2. I’m using AdventureWorksLT as my test db.

QUERY #1

A Simple Query

Let’s just grab customers with this code:

Dim custs = aw.Customer

While I might lazily write TSQL that looks like

select * from Customer

EF creates a more explicit query, which makes perfect sense to me.

SELECT
0 AS [C1],
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[NameStyle] AS [NameStyle],
[Extent1].[Title] AS [Title],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[MiddleName] AS [MiddleName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Suffix] AS [Suffix],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[SalesPerson] AS [SalesPerson],
[Extent1].[EmailAddress] AS [EmailAddress],
[Extent1].[Phone] AS [Phone],
[Extent1].[PasswordHash] AS [PasswordHash],
[Extent1].[PasswordSalt] AS [PasswordSalt],
[Extent1].[rowguid] AS [rowguid],
[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [SalesLT].[Customer] AS [Extent1]

QUERY #2

When I just want to get one particular record, I ask for there are a few ways to do this.

First is to write a query that returns the whole iQueryable with a where clause to filter for the single item. Then I iterate through that one item “list”. Using method or query syntax:

Dim cust = aw.Customer.Where(Function(c) c.CustomerID = 37)
For Each c In cust
   Dim name = c.CompanyName
Next

Dim cust2 = From c In aw.Customer Where c.CustomerID = 37 Select c
  For Each c In cust2
   Dim name = c.CompanyName
  Next

I get a reasonable TSQL query on the other end

SELECT
0 AS [C1],
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[NameStyle] AS [NameStyle],
[Extent1].[Title] AS [Title],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[MiddleName] AS [MiddleName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Suffix] AS [Suffix],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[SalesPerson] AS [SalesPerson],
[Extent1].[EmailAddress] AS [EmailAddress],
[Extent1].[Phone] AS [Phone],
[Extent1].[PasswordHash] AS [PasswordHash],
[Extent1].[PasswordSalt] AS [PasswordSalt],
[Extent1].[rowguid] AS [rowguid],
[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [SalesLT].[Customer] AS [Extent1]
WHERE 37 = [Extent1].[CustomerID]

QUERY #3

But watch what happens to the TSQL when I make my query more efficient, regardless of method or query syntax.

Dim cust = aw.Customer.Where(Function(c) c.CustomerID = 37).First
 Dim name = cust.CompanyName

SELECT
[Limit1].[C1] AS [C1],
[Limit1].[CustomerID] AS [CustomerID],
[Limit1].[NameStyle] AS [NameStyle],
[Limit1].[Title] AS [Title],
[Limit1].[FirstName] AS [FirstName],
[Limit1].[MiddleName] AS [MiddleName],
[Limit1].[LastName] AS [LastName],
[Limit1].[Suffix] AS [Suffix],
[Limit1].[CompanyName] AS [CompanyName],
[Limit1].[SalesPerson] AS [SalesPerson],
[Limit1].[EmailAddress] AS [EmailAddress],
[Limit1].[Phone] AS [Phone],
[Limit1].[PasswordHash] AS [PasswordHash],
[Limit1].[PasswordSalt] AS [PasswordSalt],
[Limit1].[rowguid] AS [rowguid],
[Limit1].[ModifiedDate] AS [ModifiedDate]
FROM   ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN  (SELECT TOP (1)
 [Extent1].[CustomerID] AS [CustomerID],
 [Extent1].[NameStyle] AS [NameStyle],
 [Extent1].[Title] AS [Title],
 [Extent1].[FirstName] AS [FirstName],
 [Extent1].[MiddleName] AS [MiddleName],
 [Extent1].[LastName] AS [LastName],
 [Extent1].[Suffix] AS [Suffix],
 [Extent1].[CompanyName] AS [CompanyName],
 [Extent1].[SalesPerson] AS [SalesPerson],
 [Extent1].[EmailAddress] AS [EmailAddress],
 [Extent1].[Phone] AS [Phone],
 [Extent1].[PasswordHash] AS [PasswordHash],
 [Extent1].[PasswordSalt] AS [PasswordSalt],
 [Extent1].[rowguid] AS [rowguid],
 [Extent1].[ModifiedDate] AS [ModifiedDate],
 0 AS [C1]
 FROM [SalesLT].[Customer] AS [Extent1]
 WHERE 37 = [Extent1].[CustomerID] ) AS [Limit1] ON cast(1 as bit) = cast(1 as bit)

What’s up with the outer join – to itself? Obviously by asking for First, which is an Element method, it changes the meaning of the query a lot. The more I think about it, if you need the flexibilitiy for First to be able to handle something like:

(From o in orders where o.Total>100 select o).First

where it’s not so obvious what the result may be as it is when filtering on a unique primary key.

I get the same effect if I use First this way, too:

Dim cust2 = From c In aw.Customer Where c.CustomerID = 37 Select c
  Dim cust = cust2.First

So that outer join is somehow necessary to pull it off. While I’d have to really push the envelope to see if there was any kind of performance difference to worry about, but it’s interesting to see the impact that using the method has on the TSQL.

QUERY #4 & QUERY #5

Next up is a table with foreign keys – 3 of them. I query for just one record (no children) in the SalesOrderHeader table.

I want to preface this by saying that I posted this on the ADO.NET Orcas forums here and was told

The Entity Framework (EF) team is aware of issues with this pattern (not the query, but the mapping). We should see huge improvements over the next couple of milestones. … The EF team is working to recognize and optimize for this common pattern, removing the redundant self-joins and including the foreign key values in a single projection.

But I didn’t want all of my exploration to go to waste, so this is what you’ll find today. We’ll just need to check back in later releases.

First I do it by getting the whole iQueryable then iterating through. Second method is to just use the First method and get only one object. I think you will quickly see why I pointed out the three foreign keys on this table.

Dim sorders = aw.SalesOrderHeader.Where(Function(so) so.SalesOrderID = 71797)
  For Each so In sorders
   Dim x = so.Comment
  Next

  Dim sorder2 = (From soh In aw.SalesOrderHeader Where soh.SalesOrderID = 71815).First
  Dim x2 = sorder2.Comment

The first query results in TSQL that creates outer joins for each foreign key, but it’s not too obnoxious.

The second is much crazier.

SELECT
[Limit1].[C1] AS [C1],
[Limit1].[SalesOrderID] AS [SalesOrderID],
[Limit1].[RevisionNumber] AS [RevisionNumber],
[Limit1].[OrderDate] AS [OrderDate],
[Limit1].[DueDate] AS [DueDate],
[Limit1].[Status] AS [Status],
[Limit1].[OnlineOrderFlag] AS [OnlineOrderFlag],
[Limit1].[SalesOrderNumber] AS [SalesOrderNumber],
[Limit1].[PurchaseOrderNumber] AS [PurchaseOrderNumber],
[Limit1].[AccountNumber] AS [AccountNumber],
[Limit1].[CreditCardApprovalCode] AS [CreditCardApprovalCode],
[Limit1].[SubTotal] AS [SubTotal],
[Limit1].[TaxAmt] AS [TaxAmt],
[Limit1].[Freight] AS [Freight],
[Limit1].[TotalDue] AS [TotalDue],
[Limit1].[Comment] AS [Comment],
[Limit1].[rowguid] AS [rowguid],
[Limit1].[ModifiedDate] AS [ModifiedDate],
[Limit1].[ShipMethod] AS [ShipMethod],
[Limit1].[ShipDate] AS [ShipDate],
[Project3].[C1] AS [C2],
[Project3].[BillToAddressID] AS [BillToAddressID],
[Project5].[C1] AS [C3],
[Project5].[ShipToAddressID] AS [ShipToAddressID],
[Project7].[C1] AS [C4],
[Project7].[CustomerID] AS [CustomerID]
FROM      ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN  (SELECT TOP (1)
 [Extent1].[SalesOrderID] AS [SalesOrderID],
 [Extent1].[RevisionNumber] AS [RevisionNumber],
 [Extent1].[OrderDate] AS [OrderDate],
 [Extent1].[DueDate] AS [DueDate],
 [Extent1].[ShipDate] AS [ShipDate],
 [Extent1].[Status] AS [Status],
 [Extent1].[OnlineOrderFlag] AS [OnlineOrderFlag],
 [Extent1].[SalesOrderNumber] AS [SalesOrderNumber],
 [Extent1].[PurchaseOrderNumber] AS [PurchaseOrderNumber],
 [Extent1].[AccountNumber] AS [AccountNumber],
 [Extent1].[ShipMethod] AS [ShipMethod],
 [Extent1].[CreditCardApprovalCode] AS [CreditCardApprovalCode],
 [Extent1].[SubTotal] AS [SubTotal],
 [Extent1].[TaxAmt] AS [TaxAmt],
 [Extent1].[Freight] AS [Freight],
 [Extent1].[TotalDue] AS [TotalDue],
 [Extent1].[Comment] AS [Comment],
 [Extent1].[rowguid] AS [rowguid],
 [Extent1].[ModifiedDate] AS [ModifiedDate],
 0 AS [C1]
 FROM [SalesLT].[SalesOrderHeader] AS [Extent1]
 WHERE 71815 = [Extent1].[SalesOrderID] ) AS [Limit1] ON cast(1 as bit) = cast(1 as bit)
LEFT OUTER JOIN  (SELECT
 [Extent2].[BillToAddressID] AS [BillToAddressID],
 2 AS [C1]
 FROM  [SalesLT].[SalesOrderHeader] AS [Extent2]
 INNER JOIN  (SELECT [Limit2].[SalesOrderID] AS [SalesOrderID], [Limit2].[C1] AS [C1]
  FROM ( SELECT TOP (1)
   [Extent3].[SalesOrderID] AS [SalesOrderID],
   0 AS [C1]
   FROM [SalesLT].[SalesOrderHeader] AS [Extent3]
   WHERE 71815 = [Extent3].[SalesOrderID]
  )  AS [Limit2]
  WHERE [Limit2].[C1] = 0 ) AS [Filter3] ON [Filter3].[SalesOrderID] = [Extent2].[SalesOrderID]
 WHERE [Extent2].[BillToAddressID] IS NOT NULL ) AS [Project3] ON cast(1 as bit) = cast(1 as bit)
LEFT OUTER JOIN  (SELECT
 [Extent4].[ShipToAddressID] AS [ShipToAddressID],
 2 AS [C1]
 FROM  [SalesLT].[SalesOrderHeader] AS [Extent4]
 INNER JOIN  (SELECT [Limit3].[SalesOrderID] AS [SalesOrderID], [Limit3].[C1] AS [C1]
  FROM ( SELECT TOP (1)
   [Extent5].[SalesOrderID] AS [SalesOrderID],
   0 AS [C1]
   FROM [SalesLT].[SalesOrderHeader] AS [Extent5]
   WHERE 71815 = [Extent5].[SalesOrderID]
  )  AS [Limit3]
  WHERE [Limit3].[C1] = 0 ) AS [Filter6] ON [Filter6].[SalesOrderID] = [Extent4].[SalesOrderID]
 WHERE [Extent4].[ShipToAddressID] IS NOT NULL ) AS [Project5] ON cast(1 as bit) = cast(1 as bit)
LEFT OUTER JOIN  (SELECT
 [Extent6].[CustomerID] AS [CustomerID],
 3 AS [C1]
 FROM  [SalesLT].[SalesOrderHeader] AS [Extent6]
 INNER JOIN  (SELECT TOP (1)
  [Extent7].[SalesOrderID] AS [SalesOrderID],
  0 AS [C1]
  FROM [SalesLT].[SalesOrderHeader] AS [Extent7]
  WHERE 71815 = [Extent7].[SalesOrderID] ) AS [Limit4] ON [Limit4].[SalesOrderID] = [Extent6].[SalesOrderID]
 WHERE [Limit4].[C1] = 0 ) AS [Project7] ON cast(1 as bit) = cast(1 as bit)

QUERY #6 & QUERY #7 (#4 & #5 converted to C#)

Now before you go blaming this on VB parsing :-), I ran the last two queries in C#:

 var so1 = aw.SalesOrderHeader.Where(so => so.SalesOrderID == 71797);
 foreach (var so in so1)
   Debug.Print(so.Comment);

  var so2 = (from soh in aw.SalesOrderHeader where soh.SalesOrderID == 71815 select soh).First();
  Debug.Print(so2.Comment);

And these resulted in the same T-SQL as their VB counterparts.

There’s more that I’ve been looking at but this post is way too long already!