Persuading the Entity Framework to sort a child collection part II

• Tweet
• Email

• Tweet
• Email

Important: I really don’t recommend doing this now, as there is a Nuget package that makes it all soooo much easier. Read more…

I previously blogged about a seemingly innocent LINQ problem that had me baffled for ages, which was how you sort or filter an entity’s child collection in Linq. For example, if you want to pull a collection of customers, and include all of their orders from this year, but none from earlier, then there doesn’t seem to be a simple way to do this in Linq. You need to do the query in two stages, the first which builds an anonymous type, and the second which links the to parts of it together. See that post for more details.

I also blogged about the problem of Linq not including child entities when doing joins, which requires you to cast the query to an ObjectQuery<> so you can use the Include() method on it.

The problem comes when you want to combine the two methods, meaning that your query needs to be constructed in two stages to ensure that the sorting or filtering of the child collection is done correctly, but you also need to cast the final result to an ObjectQuery<> before you send it out over WCF. The problem arises because you need to enumerate the query before doing the Include(), as that is the only way to ensure that the sorting is done, but calling AsEnumerable() gives you an IEnumerable<> (reasonably enough), which can’t be cast to an ObjectQuery! So what’s a fellow to do? Good question, and one that had me going for ages.

The only way I have found top do this is to enumerate the collection manually. I added a foreach loop that went through the parent collection and enumerated the children as it went along. I used Debug.Writeline() to dump the results to the Output window, which is one of my favourite debugging techniques. Anyone looking at the code would logically assume that this loop could be removed for the production code (which is I think what happened when I first wrote it), but this would cause the sorting to fail.

I ended up with code like that shown below (read to the end of this blog post to see an improved version of this code). You don’t need to know what the entities represent, just that I wanted a collection of DhrTemplates, each of which has a number of DhrTemplatesPart entities, and these had to be sorted on the DisplayPosition property.

var dhrTemplatesVar =
  from dt in getContext().DhrTemplates
    where dt.CurrentTemplate
    select new {
      currentTemplate = dt,
      templateParts = dt.DhrTemplatesParts
                        .OrderBy(dtp => dtp.DisplayPosition)
    };
Debug.WriteLine("Enumerating the collections...");
foreach (var dtmpl in dhrTemplatesVar) {
  DhrTemplate dt = dtmpl.currentTemplate;
  Debug.WriteLine("PartDefinitionID: " + dt.PartDefinitionID);
  IOrderedEnumerable<DhrTemplatesPart> parts = dtmpl.templateParts;
  foreach (DhrTemplatesPart dtp in parts) {
    Debug.WriteLine("  " + dtp.Description);
  }
}
Debug.WriteLine(" ");
ObjectQuery<DhrTemplate> dhrTemplatesQry =
  (from dt in dhrTemplatesVar select dt.currentTemplate)
    as ObjectQuery<DhrTemplate>;
if (dhrTemplatesQry != null) {
  ObjectQuery<DhrTemplate> dhrTemplates =
    dhrTemplatesQry
    .Include("User")
    .Include("PartDefinition")
    .Include("DhrTemplatesParts.PartDefinition");
  List<DhrTemplate> dhrTemplatesCurrent = dhrTemplates.ToList();
  return dhrTemplatesCurrent;
}
return null;

The OrderBy clause on line 5 orders the parts correctly, but isn’t in effect until the query is enumerated, which is what happens between lines 8 and 15. I don’t actually need the Debug.WriteLine statements any more, but I left them in case I need to come back to this again. By the time you get to line 17, you still have the anonymous type for the query, but now it has been enumerated, so the sorting is done. Now we can cast it to an ObjectQuery<> and use Include() to include the child entities.

Quite an insidious problem, but obvious when you see the solution. I still have this feeling that there should be a better way to do it though.

Update some time later…

Well of course, there was a much better way to do it! Sadly, I was so stuck in the problem that I missed the blindingly obvious answer. As mentioned, calling AsEnumerable() enumerates the query, but gives you an IEnumerable<>, which can’t be cast to an ObjectQuery. However, you don’t have to take the returned value from AsEnumerable(), you can just call it, and carry on using your original query, which has now been enumerated.

This makes the resulting code much simpler…

var dhrTemplatesVar =
  from dt in getContext().DhrTemplates
  where dt.CurrentTemplate
  select new {
    currentTemplate = dt,
    templateParts = dt.DhrTemplatesParts.OrderBy(dtp => dtp.DisplayPosition)
  };
dhrTemplatesVar.AsEnumerable();
ObjectQuery<DhrTemplate> dhrTemplatesQry =
  (from dt in dhrTemplatesVar
    select dt.currentTemplate) as ObjectQuery<DhrTemplate>;
// rest of the code omitted as it's identical

Notice that lines 7 to 16 in the previous listing have been replaced with the single line 7 in this listing. I’m calling AsEnumerable(), but ignoring the return value. This enumerates the query, but doesn’t leave me with an ObjectQuery.

Pretty obvious really!