EF-是否在IQueryable上与众不同? [英] EF - DistinctBy on an IQueryable?

问题描述

说我想让第42组的每个人都在这里，但是我想获取数据库中每个唯一名字的IQueryable(PS-我知道我可以在Select之后调用AsQueryable()，但这不是我有兴趣做的事情-我希望数据库执行不同的程序，而不是程序):

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

据我所知，EF/LINQ to Entities如何处理DistinctBy扩展方法，当调用DistinctBy时执行存储查询，并检索数据库中与Where匹配的所有项目的列表，然后C#返回来自DistinctBy方法的IEnumberable，与发送给DistinctBy的表达式匹配.

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.

我对能够高效地执行此操作感兴趣，希望通过使store查询仅返回表中所有唯一名字的结果集.例如，我可能想将IQueryable作为存储库的一部分返回，由于性能原因，由DistinctBy抓取和处理数百万个项目以仅返回唯一值是不可行的.这会将请求处理时间增加到无法接受的水平.

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.

推荐答案

我写了这个扩展名:

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

根据属性做出不同的选择，例如ID，我只是打电话给

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

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

这篇关于EF-是否在IQueryable上与众不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！