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

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!