title: Linq to Entities Select Distinct
body: <p>I'm not sure what I'm missing but I've been thrashing at this one all afternoon.</p>
  
  <p>I have a Sql Server view of Companies which looks like this:</p>
  
  <pre><code>CompanyId varchar(30) NOT NULL
  CompanyName varchar(100) NOT NULL
  CompanyPriority int NULL
  ConfigItem int NOT NULL
  </code></pre>
  
  <p>With data that looks a bit like this:</p>
  
  <pre><code>00001 | Company One | 99 | 123
  00001 | Company One | 99 | 456
  00001 | Company One | 99 | 789
  00002 | Company Two | 99 | 123
  00002 | Company Two | 99 | 456
  </code></pre>
  
  <p>I'm trying to get a distinct list of companies.  The sql query I want to exectute is </p>
  
  <pre><code>select distinct CompanyId, CompanyName,CompanyPriority from vwCompany
  </code></pre>
  
  <p>which gives me exactly the results I want which would be</p>
  
  <pre><code>00001 | Company One | 99
  00002 | Company Two | 99
  </code></pre>
  
  <p>But for the life of me I can't find the LINQ query that results in this sql, or anything that produces the same results.</p>
  
  <p>All of the questions I've found use grouping which works fine in my unit tests but fails to return distinct results when executed against an actual database.</p>
  
  <p>EDIT:</p>
  
  <p>So I've tried a few things based on the answers so far.  </p>
  
  <pre><code>Dim data = _miRepository.GetCompanies().
    Select(Function(c) New With {
             .companyId = c.CompanyId,
             .companyName = c.CompanyName,
             .companyPriority = c.CompanyPriority
           }
         ).Distinct().ToList()
  </code></pre>
  
  <p>generates the sql </p>
  
  <pre><code>SELECT 
  1 AS [C1], 
  [Extent1].[CompanyId] AS [CompanyId], 
  [Extent1].[CompanyName] AS [CompanyName], 
  [Extent1].[CompanyPriority] AS [CompanyPriority]
  FROM (SELECT 
        [vwCompany].[CompanyId] AS [CompanyId], 
        [vwCompany].[CompanyName] AS [CompanyName], 
        [vwCompany].[CompanyPriority] AS [CompanyPriority], 
        [vwCompany].[CiId] AS [CiId]
        FROM [dbo].[vwCompany] AS [vwCompany]) AS [Extent1]
  </code></pre>
  
  <p>which doesn't have the distinct operator in it at all  :(</p>
  
  <p>And yes, I'm doing this in VB.NET just to make it harder to find good examples  :\</p>
  
  <p>EDIT 2:</p>
  
  <p>I'm trying to get as close to Eric Js answer as I can in VB.</p>
  
  <pre><code>Dim data = (From c In _miRepository.GetCompanies()
              Select New With {.companyId = c.CompanyId,
                               .companyName = c.CompanyName,
                               .companyPriority = c.CompanyPriority
                              }
                            ).Distinct().ToList()
  </code></pre>
  
  <p>gives me </p>
  
  <pre><code>SELECT 
  1 AS [C1], 
  [Extent1].[CompanyId] AS [CompanyId], 
  [Extent1].[CompanyName] AS [CompanyName], 
  [Extent1].[CompanyPriority] AS [CompanyPriority]
  FROM (SELECT 
        [vwCompany].[CompanyId] AS [CompanyId], 
        [vwCompany].[CompanyName] AS [CompanyName], 
        [vwCompany].[CompanyPriority] AS [CompanyPriority], 
        [vwCompany].[CiId] AS [CiId]
        FROM [dbo].[vwCompany] AS [vwCompany]) AS [Extent1]
  </code></pre>
  
  <p>Still no distinct keyword to be found :(</p>
  
  <p>Maybe there's a subtle difference in VB.NET that I'm missing.</p>
  
  <p>EDIT 3:</p>
  
  <p>In order to progress with the rest of this application I've given up for the moment and created a new view (vwDistinctCompanies) using the sql statement at the start of the question.</p>
  
  <p>If anyone manages to get this working in VB.NET against a Sql view please let me know.  Quite why this should be so difficult in LINQ I have no idea  :(</p>