Group or Order by sub type using LINQ to Entities

It's a little hard to see how to group by a sub type in Linq to Entities. Since the discriminating field (when using table per hierarchy inheritance) or table name (when using table per type inheritance) is of course not exposed past the ORM layer. This essentially leaves us with the type of the object to discriminate sub types.

If we try to use GetType() in a LINQ to Entites query we get an exception of the sort:
"LINQ to Entities does not recognize the method 'System.Type GetType()' method, and this method cannot be translated into a store expression."

On the other hand however EF can recognise the 'is' keyword. Now lets see how to group by using this new found knowledge.

Scenario: A PERSON is subdivided into ADULT, TEENAGER, etc like below:

public class Person  
{
    public int Age { get; set; }
}

public class Teenager : Person { //... }  
public class Adult : Person { //... }

Now how do I find the count of people who are adults and teenagers in one LINQ query? (without ToList() as this no longer performs the query in SQL)
The trick is to to use nested ternary operators inside of your GroupBy keySelector parameter using the 'is' keyword.

var results = Context.Persons  
    .GroupBy(p => p is Adult? "Adult" : p is Teenager ? "Teenager" : "", (key, results) =>
    {
        k,
        results.Count()
    }).ToDictionary(p => p.key);

The same can be done for OrderBy queries. Hope this helps you as it did me.

comments powered by Disqus