CODEWITHSUNDEEP
c#linqentity-framework
DHickey
DHickey

Reputation: 13

Using DISTINCT on a subquery to remove duplicates in Entity Framework

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

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.

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

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

IDEAL sql is:

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

Thanks Dave

Upvotes: 1

Views: 5595

Answers (1)

Magnus
Magnus

Reputation: 46977

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

Try this:

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

Upvotes: 6

Related Questions