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<string> 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 IEnumerable<T> 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<string> 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.

score 29 · Answer 1 · edited Jun 02 '21 at 17:11

29

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);

edited Jun 02 '21 at 17:11

Grigory Zhadko

1,484
1
19
33

answered Nov 13 '16 at 12:01

Tomas

449
5
11

score 15 · Answer 2 · answered Sep 16 '15 at 22:00

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()

EF - DistinctBy on an IQueryable?

2 Answers2

Linked