title: Using DISTINCT on a subquery to remove duplicates in Entity Framework
body: <p>I have question about use of Distinct with Entity Framework, using Sql 2005.  In this example:</p>
  
  <pre><code>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 =&gt; p.UpdateDate); 
  
  data = practitioners.Skip(PageSize * (pageOffset ?? 0)).Take(PageSize).ToList();
  </code></pre>
  
  <p>It all works fine, but the use of <code>distinct</code> is very inefficient.  Larger result sets incur unacceptable performance.  The <code>DISTINCT</code> 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 <code>DISTINCT</code> on the ProviderNetworks, the query runs much faster.</p>
  
  <p>How can I cause EF to add the <code>DISTINCT</code> only the subquery, not to the entire resultset?</p>
  
  <p>The resulting simplified sql I DON'T want is: </p>
  
  <pre><code>select DISTINCT p.* from Providers 
  inner join Networks pn on p.ProviderId = pn.ProviderId
  where NetworkName in ('abc','def')
  </code></pre>
  
  <p>IDEAL sql is:</p>
  
  <pre><code>select p.* from Providers 
  inner join (select DISTINCT ProviderId from Networks 
              where NetworkName in ('abc','def')) 
  as pn on p.ProviderId = pn.ProviderId
  </code></pre>
  
  <p>Thanks
  Dave</p>