Master-Details with Entity Framework Explicit Load

Master-Details with Entity Framework Explicit Load

  • Comments 22

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):

edm1

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.

edm2

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!

Leave a Comment
  • Please add 5 and 1 and type the answer here:
  • Post
  • 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.

  • 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.

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

  • 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

  • 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

  • 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

  • I wish I saw this about 4 months ago....

    Thanks.

Page 2 of 2 (22 items) 12