Vb.net – Linq to Entities Select Distinct

distinctentity-frameworklinq-to-entitiesvb.net

I'm not sure what I'm missing but I've been thrashing at this one all afternoon.

I have a Sql Server view of Companies which looks like this:

CompanyId varchar(30) NOT NULL
CompanyName varchar(100) NOT NULL
CompanyPriority int NULL
ConfigItem int NOT NULL

With data that looks a bit like this:

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

I'm trying to get a distinct list of companies. The sql query I want to exectute is

select distinct CompanyId, CompanyName,CompanyPriority from vwCompany

which gives me exactly the results I want which would be

00001 | Company One | 99
00002 | Company Two | 99

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.

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.

EDIT:

So I've tried a few things based on the answers so far.

Dim data = _miRepository.GetCompanies().
  Select(Function(c) New With {
           .companyId = c.CompanyId,
           .companyName = c.CompanyName,
           .companyPriority = c.CompanyPriority
         }
       ).Distinct().ToList()

generates the sql

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]

which doesn't have the distinct operator in it at all 🙁

And yes, I'm doing this in VB.NET just to make it harder to find good examples :\

EDIT 2:

I'm trying to get as close to Eric Js answer as I can in VB.

Dim data = (From c In _miRepository.GetCompanies()
            Select New With {.companyId = c.CompanyId,
                             .companyName = c.CompanyName,
                             .companyPriority = c.CompanyPriority
                            }
                          ).Distinct().ToList()

gives me

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]

Still no distinct keyword to be found 🙁

Maybe there's a subtle difference in VB.NET that I'm missing.

EDIT 3:

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.

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 🙁

Best Answer

Try using .Distinct() at the end of your query, e.g.

(from r in ctx.MyTable where SomeCondition select r).Distinct();

http://msdn.microsoft.com/en-us/library/system.linq.enumerable.distinct.aspx

If needed, you can also provide an IEqualityComparer. However, to use the IEqualityComparer, the resulting enumerable must first be materialized using .ToEnumerable(). Doing this means the Distinct() operation is performed on the client rather than on the DB server.

http://msdn.microsoft.com/en-us/library/bb338049.aspx

The IEqualityComparer allows you to control exactly which records are treated as equal and therefore not distinct from other records.

If you want to select only a subset of columns of your table, change

select r

to select either an anonymous type like this:

(from r in ctx.MyTable where SomeCondition
    select new 
    { 
        CompanyId = r.CompanyId, 
        CompanyName = r.CompanyName, 
        CompanyPriority = r.CompanyPriority
    }
).Distinct();

or if you need a strongly typed object (for example, because you are using strongly typed views in MVC:

public class CompanyModel
{
    public int CompanyId { get; set; }
    public string CompanyName { get; set; }
    public int CompanyPriority { get; set; }
}

// Then in the Linq statement

(from r in ctx.MyTable where SomeCondition
    select new CompanyModel()
    {
        CompanyId = r.CompanyId, 
        CompanyName = r.CompanyName, 
        CompanyPriority = r.CompanyPriority
    }
).Distinct();
Related Topic