Master-Details with Entity Framework Explicit Load

Unlike LINQ to SQL, in Visual Studio 2008 Service Pack 1 explicit loading is the default behavior in the Entity Framework. When a query is executed within the Entity Framework, the entities returned by the query are fully accessible, but any associated entities are not immediately loaded. This means that if we have a Master-Detail relationship in our model, say Customer related to many Orders, and we query for a Customer then only the Customer entity will be returned, not the related Orders.

Now LINQ to SQL will load any related entities by default when/if they are accessed, meaning that LINQ to SQL lazily loads the related data by default when it's accessed. The EF team wanted to make this much more explicit. They believe that a developer should always be very aware when data is being fetched and I tend to agree here. All the business applications I have ever written were very careful about how much data was being loaded and how often network/service calls were being made. This is very important to the scalability of your solutions. In any data application you need to always balance the amount of data being returned versus the number of queries you need to make to your database, and that can be very expensive depending how "far" away your database is from the caller.

Implementing lazy loading works well when you only need to make a few additional calls for the related data or the data is optional (like triggered by a user action). On the other hand, if you know you will always need the related data then it is probably more efficient to load the related entities in a single call. This is called eager loading -- there is more data returned to you but there is less chit-chat between the client and the database.

So although you have to be explicit about loading related entities with EF, this gives you the power to decide how to best fit these loading techniques in your applications. And luckily the code is straightforward. Let's take a look.

Creating a Simple Master-Detail Model

In order to illustrate these loading techniques let's create a simple one-to-many example using our favorite database, Northwind. Using Visual Studio 2008 Service Pack 1 I've added a new item to the project and selected Entity Data Model, chose the "Generate from Database" option, and selected Customers and Orders tables from Northwind. So here's my Entity Data Model (EDM):

Setting up Windows Form Data Binding to an EDM

Let's display this data on a Windows Form in two related DataGridViews. You can design the form using the data sources window against the Customer and Order entities in the model, you just need to select Data on the main menu and then Add New Data Source. When the Data Source Configuration wizard opens select Object data source and then select the Customers entity. (BTW, this is exactly how you would do it if using LINQ to SQL classes or your own business objects). The related Orders collection will automatically be pulled in so just select Customers and click Next.

Now you can use your data sources window to design your windows form. Drag the Customers entity as a DataGridView onto a Windows form and then drag the related Orders entity onto the form below the Customers to set up a Master-Detail form. (Sorry if this is a recap for you all but I always get questions on this when I assume people know this! ;-))

Lazy Loading Related Entities

Okay back to the point. First write a query that selects the Customer who's CustomerID is ALFKI. Then we can set the CustomersBindingSource.DataSource property to the query result and it will execute the query on the load of our form.

 Public Class Form1

    Private db As New NorthwindEntities

    Private Sub Form1_Load() Handles MyBase.Load

        Dim query = From c In db.Customers _
                    Where c.CustomerID = "ALFKI"

        Me.CustomersBindingSource.DataSource = query.ToList()

    End Sub
End Class

Run the form and you'll see that we are only getting Customer ALFKI, and none of the related Orders. If you do this exact code above with LINQ to SQL classes then it would execute two queries on load and you would see the related Orders. This is because the CustomerBindingSource requests the collection of Customers so it executes the query that we wrote above. When the parent BindingSource's CurrentItem changes when the results are returned then it triggers the child BindingSource to display any related data. With LINQ to SQL this causes an additional query to execute to pick up the children. So as the position moves in the parent, the system is actually hitting the database to get the children in the case of LINQ to SQL.

To do this with EF we can handle the CurrentChanged event of the BindingSource and explicitly load the Orders for that Customers entity:

 Private Sub CustomersBindingSource_CurrentChanged() Handles CustomersBindingSource.CurrentChanged
   Dim c = TryCast(Me.CustomersBindingSource.Current, Customers)

   If c IsNot Nothing Then
       If Not c.Orders.IsLoaded Then
           c.Orders.Load()
       End If
   End If

End Sub

Now when we run this again you will see the related Orders and two queries will execute against the database.

Eager Loading Entities

But what if we don't want to make two separate queries against the database? In the scenario above we always want to display the related Orders with the Customers so it would be better to make a single call to the database to retrieve the Customer and the Orders. In that case we can use the Include method that is available on an entity and we add this to our query in our form's Load:

 Dim query = From c In db.Customers.Include("Orders") _
            Where c.CustomerID = "ALFKI"

Now when we run the form again we will see the same results, but this time there is only one query being made against the database. So although EF forces us to think harder about how our data should be loaded, this is a generally good thing for most business application development. It's up to us developers to determine the best technique for our situations.

Enjoy!

Comments

• Anonymous
  December 10, 2008
  There is a major flaw in all this that the behavior is completely non-obvious. If the customer's orders are not loaded you don't get any warning. Instead you just get back an empty collection. http://www.hackification.com/2008/12/04/linq-to-entities-follow-up/

• Anonymous
  December 11, 2008
  The fact that Link To Entity does not throw an Exception when the child collection is not loaded makes it very hard to track down bugs.   Most of my code should know nothing about how the data is loaded and just fail (or automatically load the data) if the data it’s need has not be prelaoded Also having to pass a string to the Include() method is very poor, as it stops the compiler finding errors if the Orders property is every renamed.  It would be better if  Link To Entity allow “Include(x-> Orders)”, hence letting refactoring work etc. If only Link-to-Sql worked with Oracle… Or if Link and all other Microsoft tecknolgy surported nhibernate as well as Link To Entity

• Anonymous
  December 11, 2008
  The comment has been removed

• Anonymous
  December 11, 2008
  Totally agree with Ian on the string syntax.  Why not have an enumeration on the entities that can be passed?

• Anonymous
  December 15, 2008
  -> Why would you want an exception when accessing the collection? Because I told the system my code does not need the values in the collection (otherwise I would have loaded it).  Then my code used the values in the collection, therefore my code has a bug, and the system should make it easy for me to find bugs in my code.

• Anonymous
  December 22, 2008
  From thinks like that is seems to me that Entity Framework is NOT READY for commercial applications! It is terrible to build a typed OR/M schema which needs a string !!!!! in order to work! If so why bother using all these stuff? I could just write sql ...

• Anonymous
  January 30, 2009
  Thanks for the useful details on lazy loading, Beth. It's exactly what I was looking for!

• Anonymous
  February 19, 2009
  The comment has been removed

• Anonymous
  February 19, 2009
  Hi Josh, I actually used the designer to hook up the child bindingsource in my example. Because there is an association between Customer and Orders the Ordersbindingsource is automatically bound to the CustomerBindingSource (it chains as you navigate the relations). The other issue you're probably running into is because you probably have a non null foreign key to your parent table from the child (as any good database should). Unfortunately the lists that you are bound to will not properly notify the objectstatemanager of the deletion (removal from the list) in that case. So when you delete the child you have to call DeleteObject(child) on the objectcontext. You will also need to delete all the children and the parent if a parent is removed from the list. It's not a limitation of Winforms or the DataGridView this will be the same issue in WPF. It's an issue with EF and binding to the lists it returns. HTH, -B

• Anonymous
  February 23, 2009
  Thanks Beth- using DeleteObject directly works fine.  This would seem to be a bit of a shortcoming in EF though, are you aware of this issue being handled in a future release?

• Anonymous
  February 25, 2009
  The comment has been removed

• Anonymous
  March 01, 2009
  Workable, yes, perfect no. But I'm easy, so it good for me. Thanks for posting the knowledge.

• Anonymous
  March 25, 2009
  Excelent Post. You are Great !!! Thanks for sharing !!!!

• Anonymous
  March 26, 2009
  I agree with Tony and Ian.   Entity Framework is very interesting but it seems too poor at the moment. On some blogs it's written Linq to sql is on an end way... EF is not ready for real enterprise application.... not a good situation.  :(

• Anonymous
  April 01, 2009
  Thanks Beth.  This is the best EF master detail example I have seen so far.  But I have a question.  How would I include a WHERE clause or ORDER BY clause in the detail table query (sorry I may not be using EF terminology).   The example has a where in the master table query and I have done order by as well.  But I can't see how to include either clause in the detail table query. Hope you can help. Thanks Scott.

• Anonymous
  April 01, 2009
  I should have also mentioned that I am primarily interested in how to include where/order by clauses in the detail table query in the lazy loading case.  However, I would also be interested to understand how to do it in the eager loading case, if it is any different. Thanks again Scott.

• Anonymous
  April 02, 2009
  Here is a technique I came up with to lazy load the Orders with an Orderby clause, but I'm not sure if it is a "recommended" method.  Please note I have translated this from C# to VB so the syntax may not be perfect! Private Sub CustomersBindingSource_CurrentChanged() Handles CustomersBindingSource.CurrentChanged   Dim c = TryCast(Me.CustomersBindingSource.Current, Customers)   If c IsNot Nothing Then       If c.Orders.Count = 0 Then           (From o in db.Orders   Where o.CustomerID = c.CustomerID   Orderby o.OrderID descending   Select o).ToList()       End If   End If End Sub I could not see how to use the Load() method after a LINQ query, but found the ToList() loaded the data I wanted.  However, it does not seem logical to get the data returned as a List, just to throw it away! Also, since I didn't use the Load() method, the IsLoaded property was not set.  Instead, I tested whether there were any related Orders for the current Customers, and ran the query if there weren't any.  I know this is not the same as testing IsLoaded, and may generate additional queries where nothing is returned, but that's the best I could come up with.   Is there a better way? Thanks Scott.

• Anonymous
  May 01, 2009
  It’s extremely common to have to hook up lookup tables on your data entry forms in order to populate

• Anonymous
  July 08, 2009
  Hello Beth, thanks for your useful tutorials. I've applied this tecnique to my project using EF v2 and VS2010. I'm using a model with POCO classes and the 2 entities used in the master-detail are implemented using proxies (all the properties are virtual and collection properties are ICollections<T>). I've noticed a problem hooking up the 2 bindingSource with the designer in this scenario. When I drag the navigation property on my form, the only dataBound columns i get are "Count" and "IsReadOnly", but at runtime the amount of rows in the datagrid is the same as in the db. So I tried to manually add the proper columns to the second datagridview, but they were always empty (while the amount of rows were still correct). Do you think there's a way to adapt master-detail datagrids with POCO classes using proxies? Thanks Matteo

• Anonymous
  July 17, 2009
  Hi Scott, Check out this post for options on how to filter the child collections: http://blogs.msdn.com/bethmassi/archive/2009/07/16/filtering-entity-framework-collections-in-master-detail-forms.aspx HTH, -B

• Anonymous
  July 17, 2009
  Hi Matteo, I haven't played with POCO in V2 of EF but it sounds like the bindings are to the proxy objects and not the entity types. I'd post your question to the EF designer team here: http://blogs.msdn.com/efdesign/ I'd be interested in knowing what's happening as well, it sounds like a bug. Cheers, -B

• Anonymous
  August 22, 2012
  I wish I saw this about 4 months ago.... Thanks.