Filtering Entity Framework Collections in Master-Detail Forms

  • Article

Last post I talked about how to get WPF data binding to work with master-detail entity collections. I had a couple readers ask me how they could filter the child collection instead of bringing them all down so in this post I’d like to show how you could do that. It’s actually pretty easy.

Customer (Master) –< Orders (Detail)

I created a very simple Entity Data Model (EDM) that demonstrates a Master-Detail relationship. The Orders table is defined in the database with a non-nullable foreign key CustomerID, meaning that no Order can exist without a Customer. This relationship is inferred by Entity Framework (EF) to set up the navigation properties giving us an Orders EntityCollection on Customer.

To recap, I’ve created my own ObservableCollection(of Customer) called CustomerCollection so that when the UI modifies the collection through AddItem/RemoveItem, we can instruct the ObjectContext to track these changes so that it will insert and delete entities to the database when we call SaveChanges.

The constructor takes an IEnumerable(Of Customer) which allows us to pass a LINQ to Entities query into our CustomerCollection. This executes the query against the database when we call the base class’s constructor which iterates over the query results and adds the Customer entities to our collection.

Take a look at the first chunk of code in the last post for the full code listing for the CustomerCollection class. I don’t have to make any changes to it to support filtering the children.

Loading All Detail Entities

If you want to bring down all the Orders for the selected Customer you can use the .Include extension like I’ve shown before using explicit loading.

 'EF ObjectContext connects to database and tracks changes
Dim db As New OMSEntities
'inherits from ObservableCollection(Of Customer)
Dim CustomerData As CustomerCollection

'Include all Orders for Customer #1
Dim customers = From c In db.Customers.Include("Orders") _
                Where c.CustomerID = 1

CustomerData = New CustomerCollection(customers, db)

This ends up shooting one query to SQL Server to return all the Orders for the selected Customer when we pass the query into our collection. But what if we also wanted to filter the Orders? There’s a couple ways we can do this because of a cool feature of the Entity Framework.

Filtering Detail Entities with a Sub-Query

When the entities are being created in memory on the client from the query (materialized), the object state manager will attempt to hook up the entity references and collections automatically. This means that all we have to do is specify a filtered query and make sure we execute it by enumerating over the results (or calling ToList).

So say we wanted to only grab the most recent Orders where the order was placed on or after January 1st, 2009:

 Dim query = From c In db.Customers _
            Where c.CustomerID = 1 _
            Select Customer = c, _
                   Orders = From o In c.Orders _                            Where o.OrderDate >= #1/1/2009# 


Dim customers = From item In query.ToList Select item.Customer

CustomerData = New CustomerCollection(customers, db)

Here I’m specifying a sub-query to only pull the Orders I want on the selected Customer. The first query creates a collection of anonymous types that have a Customer object and a filtered Orders collection. In the following query, I need to call query.ToList in order to execute the query against the database and materialize the objects. This sends one statement to SQL Server like before, but this time it’s filtered on Orders as well.

At this point the Customers and Orders are in memory and the object state manager has hooked them up for us. Meaning that the Customer now has an Orders collection like we want. As long as we Select the entire Customer and Order types in our queries this will work (as opposed to just selecting specific fields – the entity types have to match up for it to work). The second query is just a LINQ to Objects query (in memory) that we’re using so that we can pass an IEnumerable(Of Customer) to our ObservableCollection.

Filtering Detail Entities with Separate Queries

There are other ways we could write this. We could write the queries separately:

 Dim customers = (From c In db.Customers _
                 Where c.CustomerID = 1).ToList()

Dim orders = (From o In db.Orders _
              Where o.Customer.CustomerID = 1 AndAlso _
                    o.OrderDate > #1/1/2009#).ToList()

CustomerData = New CustomerCollection(customers, db)

Notice that I’m calling ToList to force the queries to execute so that the objects will materialize (come into memory), then I’m just passing the Customers to the constructor because at this point the object state manager has hooked up our entities (Customer has an EntityCollection of Orders based on the filtered query). The results on the client are the same but the major difference here is that two statements are sent to SQL Server, so keep that in mind. I like the first approach better with a sub-query because of the single SQL statement that’s generated as well as the clearer looking LINQ query.

I’ve updated this sample application that also shows a variety of other data binding techniques with EF and WPF so have a look. The filter example above is in the SimpleMasterDetailBinding form.

Enjoy!