EF - DistinctBy on IQueryable?

Question

Say that I have this where I want to get every person in group 42, but I want to get IQueryable of each unique name in the database (PS - I know that I can call AsQueryable () after Select, but not what interests me - I want the database to run a separate, not a 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 say about how EF / LINQ to Entities handles the DistinctBy extension method, the storage request is executed when DistinctBy is called and displays a list of ALL items in the database matching the Where query, then C # returns IEnumberable from the DistinctBy method, which matches the expression sent to DistinctBy.

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

I am interested in doing this efficiently, I hope if the query to the repository returns only the result set of all the unique FirstNames in the table. For example, I can return this IQueryable as part of a repository, where for performance reasons it is not so good to have millions of elements captured and processed by DistinctBy to simply return unique values. This would increase the processing time of the request to an unacceptable level.

Is there any way to do this? Am I missing something? This is just a simple example, it is obvious that in real objects of the application, more complex than a string, will be the subject of a request 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()); } 

Make a separate selection based on a property, for example. Id, I just call:

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

Answer 2

With IQueryable you need to do this yourself:

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

The MoreLinq DistinctBy method is an extension method on IEnumerable<T> , so it accepts IQueryable<T> , but inside the method it acts like IEnumerable<T> . This is the same effect as when db.People.AsEnumerable().DistinctBy() executed