在子查询上使用DISTINCT删除实体框架中的重复项 [英] Using DISTINCT on a subquery to remove duplicates in Entity Framework

问题描述

我对使用Sql 2005在实体框架中使用Distinct有疑问.在此示例中:

I have question about use of Distinct with Entity Framework, using Sql 2005. In this example:

practitioners = from p in context.Practitioners
                join pn in context.ProviderNetworks on
                     p.ProviderId equals pn.ProviderId
                (notNetworkIds.Contains(pn.Network))
                select p;

practitioners = practitioners
                  .Distinct()
                  .OrderByDescending(p => p.UpdateDate); 

data = practitioners.Skip(PageSize * (pageOffset ?? 0)).Take(PageSize).ToList();

一切正常，但是distinct的使用效率很低.较大的结果集会导致无法接受的性能. DISTINCT杀死了我.仅由于需要查询多个网络，才导致需要提供唯一性，因此会重复提供者的记录.实际上，我需要询问数据库即使它们位于多个网络中，也只能返回提供者一次".如果我可以将DISTINCT放在ProviderNetworks上，查询运行速度会更快.

It all works fine, but the use of distinct is very inefficient. Larger result sets incur unacceptable performance. The DISTINCT is killing me. The distinct is only needed because multiple networks can be queried, causing Providers records to be duplicated. In effect I need to ask the DB "only return providers ONCE even if they're in multiple networks". If I could place the DISTINCT on the ProviderNetworks, the query runs much faster.

如何使EF仅将DISTINCT子查询而不是整个结果集添加?

How can I cause EF to add the DISTINCT only the subquery, not to the entire resultset?

我不需要的结果简化的sql是:

The resulting simplified sql I DON'T want is:

select DISTINCT p.* from Providers 
inner join Networks pn on p.ProviderId = pn.ProviderId
where NetworkName in ('abc','def')

理想的sql是:

select p.* from Providers 
inner join (select DISTINCT ProviderId from Networks 
            where NetworkName in ('abc','def')) 
as pn on p.ProviderId = pn.ProviderId

谢谢 戴夫

推荐答案

我认为您在这里不需要Distinct，而需要Exists(或在Linq中称为Any)

I dont think you need a Distinct here but a Exists (or Any as it is called in Linq)

尝试一下:

    var q = (from p in context.Practitioners
            where context.ProviderNetworks.Any(pn => pn.ProviderId == p.ProviderId && notNetworkIds.Contains(pn.Network))
            orderby p.UpdateDate descending
            select p).Skip(PageSize * (pageOffset ?? 0)).Take(PageSize).ToList();

这篇关于在子查询上使用DISTINCT删除实体框架中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！