LINQ to SQL select distinct from multiple colums

Question

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.

how's your database design? From the query you gave, it is not possible to have duplicate items except that your db really got duplicate items (that id and name are not the only fields there?) — xandy, Apr 02 '10 at 16:54
The Table is just log-table for customer order. So, yes it's possible to store the same CustomerId in the column. — Narazana, Apr 02 '10 at 17:02
It works fine for me (with a List(Of T), didn't try with a Linq to SQL entity set...) — Thomas Levesque, Apr 02 '10 at 20:59

score 2 · Accepted Answer · edited May 23 '17 at 12:24

The trouble you're having is that VB.NET treats the objects returned from a Linq query differently than C# does, which is why a lot of the answers here are from baffled C# developers. VB.NET returns mutable objects from Linq queries. C# returns immutable objects. So, in C# equality is already handled for you, but in VB.NET you have to specify which fields are considered equal using the Key keyword. You can see this easily in LinqPad yourself:

Dim items As New List(Of KeyValuePair(Of Integer, String))()
items.Add(New KeyValuePair(Of Integer, String)(1, "David"))
items.Add(New KeyValuePair(Of Integer, String)(2, "James"))
items.Add(New KeyValuePair(Of Integer, String)(3, "Smith"))
items.Add(New KeyValuePair(Of Integer, String)(2, "James"))
items.Add(New KeyValuePair(Of Integer, String)(5, "Joe"))

items.Dump()

Dim uhOhResult = (from a in items select New With {a.Key, a.Value}).Distinct()
usOhResult.Dump()
Dim distinctResult = (from a in items select New With {Key a.Key, Key a.Value}).Distinct()
distinctResult.Dump()

In your example, put the Key keyword in to define which fields participate in the equality check, and distinct will work properly.

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

See here: Linq Group on Multiple Fields - VB.NET, Anonymous, Key and here: Distinct in LINQ with anonymous types (in VB.NET)

score 1 · Answer 2 · answered Sep 30 '16 at 14:55

1

you could use this approach:

Dim distinctResult = customers.GroupBy(Function(cus) New With {Key cus.CustomerId, Key cus.CustomerName}) _
                              .Select(Function(cus) cus.First()) _
                              .ToList()

answered Sep 30 '16 at 14:55

Massimo Della Calce

406
4
17

score 1 · Answer 3 · answered Mar 29 '12 at 09:51

    Dim customer = From cus In db.Customers Order By cust.CustomerID Select cus.CustomerId, cus.CustomerName 

    For Each c In customer.Distinct()
        Listbox1.Items.Add(c.CustomerId & " " & c.CustomerName)
    Next

Should give you a list of DISTINCT Customer Names and IDs. Not sure about the Ordering.

score 0 · Answer 4 · answered Apr 02 '10 at 23:35

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

LINQ to SQL select distinct from multiple colums

4 Answers4