Filtering child collections with Entity Framework 4 and LINQ

Today I spent a stupid amount of time working on something that should have been incredibly simple. I’m working on a project that’s using Entity Framework 4 for its data access, and everything seems to be an uphill battle. I’m getting better with it, but today’s fun led me to post this Facebook status update:

Entity Framework 4, it would appear that you’re just like every other “awesome” technology from Microsoft that isn’t just a base language and compiler (I still love you c#/.net). Great looking from the outside, a steaming pile of dung on the inside once someone tries to use you for anything more than a flashy demo project.

Here’s the breakdown of what I was trying to accomplish:

  • I have three tables: Product, Item (child of Product, holds various sizes of the product – this is what the customer buys) and ItemPrice (a child of Item – Items can have customer specific pricing, but only one price is applicable at a time).
  • I’m showing a list of all items for a Product, including their price for a specific customer.

You can imagine the method signature:

List<Item> GetItemsByProductID(int productID, string customerCode)

You can probably also imagine the raw SQL that would accomplish this task. It’d take me roughly 15 seconds to write it. This, however, is an Entity Framework project! LINQ! NEW STUFF!!! IMAGINE THE POSSIBILITIES!!! <ahem>

Once it came time to write the LINQ query, I was stumped. If i just got the items and then Lazy-loaded the ItemPrices, I’d get all the prices for all the customers. Sure, I could filter this out, but it seems inefficient to pull all of that out and then discard all but one. I couldn’t join to the ItemPrice table, because some customers might not have pricing for all Items, but I’d still want them back in the results.

This is a problem that’s been solved – just ask Google. The usual solution goes something like this:

var result = (from item in entities.Items
    where item.ProductID == productID
    orderby item.Name
    select new {
        itemPrice = (
            from itemPrice in item.ItemPrices where
            itemPrice.CustomerCode == rci.CustomerCode
            select itemPrice)

(Side note – how the hell are you supposed to format LINQ queries?)

AsEnumerable is required for some reason (I didn’t research exactly why because I was dead tired of dealing with this already). The idea is that you then select just the item part of the anonymous type into a list. Entity Framework will magically hook the item prices that were retrieved behind the scenes and you’ll be set. So doing this:

var items = result.Select(i =&gt; i.item).Distinct().ToList();

Should give me the results that I want (Distinct isn’t technically required in my case since I’m expecting a 1:1 matching) . However, when I tried this the anonymous type was fine (the itemPrice collections had exactly what I was looking for), but as soon as I hit the ItemPrices collection on an Item, Lazy Loading took over and all the Prices were loaded. Blergh.

The solution is easy enough – turn off Lazy Loading:

entities.ContextOptions.LazyLoadingEnabled = false;

Not one single post that had the solution in there mentioned this. NOT. ONE. It’s pretty obvious of course, but after an hour or two of feeling frustrated that this common lookup is cryptically hidden in LINQ/Entity Framework my mind wasn’t exactly thinking straight. I’m posting this not so much to call out the solution, but to call out the obvious for anyone that’s spending a stupid amount of time on this.

  1. This type of query would be easier if Filtered Include were implemented in EF. You can vote for the feature here:

  2. While we wait for this feature to be implemented, there appears to be a work around for now:
    var blog = context.Blogs.Find(1);

    // Load the posts with the ‘entity-framework’ tag related to a given blog
    .Collection(b => b.Posts)
    .Where(p => p.Tags.Contains(“entity-framework”)

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>