EF - DistinctBy on an IQueryable?

Question

Say I have this where I want to get every person in group 42, but I want to get an IQueryable of each unique first name in the database (PS - I know I can call AsQueryable() after the Select but that's not what I'm interested in doing - I want the database to perform the distinct, not the program):

MyEfContextContainer db = new MyEfContextContainer();
IQueryable<string> uniqueFirstNames = 
    db.People
    .Where(x=> x.GroupId == 42)
    .DistinctBy(c=> c.FirstName)
    .Select(c=> c.FirstName);

From what I can tell of how EF/LINQ to Entities handles the DistinctBy extension method, a store query is executed when DistinctBy is invoked and a list of ALL items in the database that match the Where is retrieved, then C# returns an IEnumberable from the DistinctBy method that matches the expression sent to DistinctBy.

If there are millions of names in the list this is very inefficient.

I am interested in being able to do this efficiently, hopefully by having the store query only return a result set of all of the unique FirstNames in the table. I might, for example, want to return that IQueryable as part of a repository where it is not okay for performance reasons to have millions of items grabbed and processed by DistinctBy to just return unique values. This would increase request processing time to an unacceptable level.

Is there any way to do this? Am I missing something? This is only a simple example, obviously in a real application objects more complex than string would be the subject of a query to the repository.

Answer 1

I wrote this extension:

public static IQueryable<TSource> DistinctBy<TSource, TKey>  (this IQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector)
{
    return source.GroupBy(keySelector).Select(x => x.FirstOrDefault());
}

To make a distinct selection based on a property, e.g. Id, I just call:

res = res.DistinctBy(x => x.Id);

Answer 2

With IQueryable you have to do it yourself:

db.People
  .Where(x => x.GroupId == 42)
  .GroupBy(c => c.FirstName)
  .Select(g => g.FirstOrDefault());

MoreLinq's DistinctBy method is an extension method on IEnumerable<T>, so it does accept an IQueryable<T>, but inside the method it acts as an IEnumerable<T>. It is the same effect as doing db.People.AsEnumerable().DistinctBy()