How to sort and filter on a child entity when using Include()

• Tweet
• Email

• Tweet
• Email

It seems we aren’t the only ones to find filtering and sorting child entity collections unnecessarily painful. Someone has been kind enough to write a Nuget package (part of a suite of them by the looks of things) that changes the following code…

IEnumerable<PriceRegion> regions =
  getContext().CreateObjectSet<PriceRegion>()
  .Include(r => r.Countries)
  .Where(r => r.Active)
  .OrderBy(r => r.Name)
  .ToList()
  .Select(r => new {
    R = r,
    C = r.Countries.Where(c => c.Active).OrderBy(c => c.Name)
  })
  .ToList()
  .Select(r => r.R);

…into this…

IEnumerable<PriceRegion> regions =
  getContext().CreateObjectSet<PriceRegion>()
  .Where(r => r.Active)
  .IncludeFilter(r => r.Countries
                       .Where(c => c.Active)
                       .OrderBy(c => c.Name));

Spot the difference? It becomes even more apparent with a more complex query.

Note that the lambda passed to the IncludeFilter() method is just plain Linq, so you can load deeper levels of navigation properties, as well as sort and filter them as you wish…

.IncludeFilter(pg => pg.PriceGroupPriceRegions
  .Where(pgpr => pgpr.Active)
  .Select(pgpr => pgpr.Countries
    .Where(c => c.Active)
    .OrderBy(c => c.CountryName)))

You’ll need to include the Z.EntityFramework.Plus.QueryIncludeFilter.EF6 Nuget package in your project to access the .IncludeFilter() method.

Make sure to read the documentation, and note specifically that IncludeFilter() does not work with the regular Include(), so you need to use one or the other, but not a mixture.

Stupendously important point to note!!!

One thing that is really not clear in the documentation is a major, but very subtle difference between the way Include() and IncludeFilter() work.

When you do the following…

PriceGroup priceGroup = GetObjectSet()
  .Include(pg => pg.PGDistributorGroups
                 .Select(pgdg => pgdg.DistributorGroup))

…Entity Framework not only loads the PriceGroupsDistributorGroups child entities, but also loads the DistributorGroup grandchildren entities. Therefore, you only need the one Include(), and do not need to do the following (which I have seen around)…

PriceGroup priceGroup = GetObjectSet()
  .Include(pg => pg.PGDistributorGroups)
  .Include(pg => pg.PGDistributorGroups
                 .Select(pgdg => pgdg.DistributorGroup))

Line 2 is totally unnecessary, as line 3 will include PGDistributorGroups

However, when using IncludeFilter(), you do need to include the extra lines. This is a limitation in the way the package works, as it only includes the last item in the query. Thus, to include both the child and grandchildren entities in the query above, as well as filter them, you need to do the following…

PriceGroup priceGroup = GetObjectSet()
  .IncludeFilter(pg => pg.PGDistributorGroups
                         .Where(pgdg => pgdg.Active)
  .IncludeFilter(pg => pg.PGDistributorGroups
                         .Where(pgdg => pgdg.Active)
                         .Select(pgdg => pgdg.DistributorGroup))

The first line includes the active PriceGroupsDistributorGroups child entities, and the second includes the grandchild DistributorGroup entities. If you only include the second line, as you would when using Include(), the PriceGroupsDistributorGroups collection will be empty (as you haven’t selected any), and so you won’t get the DistributorGroups either.

Not-so-stupendously important note, but still worth noting

Similar to the above, if you have an entity with a navigation property that is a collection of joining entities, and you want to sort on the grandchildren, there’s a slight issue you need to note. To make this clearer, imagine your PriceGroup entity has a collection of PriceGroupPriceRegion child entities, and each of these has a (single) PriceRegion child property. You want to get the price group, and have the price regions sorted by PriceRegion.Name.

The first thought might be to do it like this (filtering code removed for clarity)…

PriceGroup priceGroup = GetObjectSet()
  .IncludeFilter(pg => pg.PriceGroupPriceRegions)
  .IncludeFilter(pg => pg.PriceGroupPriceRegions
    .Select(pgpr => pgpr.PriceRegion)
    .OrderBy(pr => pr.Name))

This makes sense, as it’s the second IncludeFilter() that selects the price region, so you’d think that’s where you need to order.

Turns out that this doesn’t work. What happens is that the first IncludeFilter() includes the joining entities in whatever order they come out of the database, and the second orders the individual price region in each joining entity. Obviously, only being one in each, this doesn’t do anything.

What you need to do is move the OrderBy() method to the first line as follows…

PriceGroup priceGroup = GetObjectSet()
  .IncludeFilter(pg => pg.PriceGroupPriceRegions
                         .Where(pgpr => pgpr.Active
                                     && pgpr.PriceRegion.Active)
                         .OrderBy(pgpr => pgpr.PriceRegion.Name))
  .IncludeFilter(pg => pg.PriceGroupPriceRegions
                         .Where(pgpr => pgpr.Active
                                     && pgpr.PriceRegion.Active)
                         .Select(pgpr => pgpr.PriceRegion))

This works as expected.