Databases
StackOverflow2013
Postsdbo
POLinq to Entities Select Distinct
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLinq to Entities Select Distinct
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNick
    1. USNick
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POLinq to Entities Select Distinct
      1. USSteven
      1. VTFavorite
    2. VO
      singulars
      1. POLinq to Entities Select Distinct
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POLinq to Entities Select Distinct
      1. This table or related slice is empty.
      1. VTUpMod
    1. COJust in case you are still trying, I tried multiple queries myself and the different ones from other users. It seem the answer by @Eric J. is giving me the SQL statement which used the `DISTINCT` keyword. I dont know how to translate that to VB as I ran them through C#
      singulars
      1. POLinq to Entities Select Distinct
      1. USSteven
    2. COThanks for trying. I couldn't get any of them to make a distinct query. I'm not sure if it's because it's in VB or if it's because it's against a view rather than a table.
      singulars
      1. POLinq to Entities Select Distinct
      1. USNick
    3. COHey, i ran further queries. Look at my edited answer. Hope it helps.
      singulars
      1. POLinq to Entities Select Distinct
      1. USSteven
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload