LINQ to SQL select distinct from multiple colums

Question

Hi,

I'm using LINQ to SQL to select some columns from one table. I want to get rid of the duplicate result also.

Dim customer = (From cus In db.Customers Select cus.CustomerId, cus.CustomerName).Distinct

Result:

• 1 David
• 2 James
• 1 David
• 3 Smith
• 2 James
• 5 Joe

Wanted result:

• 1 David
• 2 James
• 3 Smith
• 5 Joe

Can anyone show me how to get the wanted result? Thanks.

Answer 1

I will answer this in c#, because Linq in VB is about as good a time as jamming a rusty fork under a toenail:

var customers = db.Customers.Select(c => new{c.CustomerID, c.CustomerName}).Distinct();

You could also do something like this:

var customers = (from c in db.Customers select new {c.CustomerID, c.CustomerName}).Distinct();

Answer 2

You should use the overload of Distinct that takes an IEqualityComparer. I answered a similar question about Except on CodeProject a while back where I included a DelegateEqualityComparer class that lets you just use a lambda or other function without needing to actually write the class implementing the comparer.

http://www.codeproject.com/Messages/3244828/Re-How-do-I-do-an-effective-Except.aspx

The advice about needing equivalent hash codes probably applies as much to Distinct as it does to Except.

To use this with anonymous types, you will probably need a helper method to create the comparer. Something like this should work if you pass in the query (before calling Distinct, of course) as the first parameter to make the type inference work.

Public Function CreateComparer(Of T)(
                   ByVal items As IEnumerable(Of T), 
                   ByVal comparison As Func(Of T, T, Boolean)
                ) As DelegateEqualityComparer(Of T)
    Return New DelegateEqualityComparer(Of T)(comparison)
End Function