Linq to Entities Select Distinct

Question

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 :(

Solution 1

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

Solution 2

-EDITED:-

Ignore all my code that I mentioned earlier (everything after the end edit section). I tried further test. Plug the following VB code and tell me what results you got:

(From c In ctx.Companies Select New With { _
    Key .companyId = c.CompanyId, _
    Key .companyName = c.CompanyName, _
    Key .companyPriority = c.CompanyPriority _
}).Distinct()

I tested them using LINQPad and I got the following SQL:

SELECT DISTINCT [t0].[CompanyId] AS [companyId],
                [t0].[CompanyName] AS [companyName],
                [t0].[CompanyPriority] AS [companyPriority]
FROM [Companies] AS [t0]

-END EDIT-

I had this problem few days ago. This is what I ended up doing.

What you are looking for would require the GroupBy clause as you mentioned in your post. Just using Distinct will not work as how you would expect (As far as I know). The following lambda expression is what I did and right after is the SQL statement that is generated through the lambda code.

Lambda Code:

Companies.GroupBy(c => new {
                           c.CompanyId,
                           c.CompanyName,
                           c.CompanyPriority
                       })
         .Select(p => new {
                          CompanyId = p.Key.CompanyId,
                          CompanyName = p.Key.CompanyName,
                          CompanyPriority = p.Key.CompanyPriority
                      })

SQL Code:

SELECT [t0].[CompanyId] AS [companyId],
       [t0].[CompanyName] AS [companyName],
       [t0].[CompanyPriority] AS [companyPriority]
FROM [Companies] AS [t0]
GROUP BY [t0].[CompanyId],
         [t0].[CompanyName],
         [t0].[CompanyPriority]

Comments

[Nick]

Yes it should, unfortunately that decision is out of my hands :(

[Nick]

Strangely it works in LinqPad but not in my application. I wonder if it's because it's against an IQuerable over an ObjectSet or whether it's just the version of EF that the project has been set up against. Unfortunately I've been drafted in halfway through to cover the original dev going on leave. When I have a little breathing room I'm going to come back to this (I've got 2hrs until sprint review and I need something working right now :\ ).