LINQ to SQL select distinct from multiple colums
Solution 1
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)
Solution 2
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.
Solution 3
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()
Narazana
Updated on June 04, 2022Comments
-
Narazana almost 2 years
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.