Linq to DataTable not producing Distinct values

Question

Hi,

I have a datatable which has been dynamically generated from FoxPro tables using a UNION Select statement. e.g.

SELECT * FROM x UNION SELECT * FROM y UNION SELECT * FROM Z ORDER By v_alue1

This produces a datatable with about 100 rows, each containing many fields, one of which is c_olor. From this datatable, I would like to select the distinct colors and then output in a dropdown.

I have a public class Color which just has one property which I can then use as the DataTextField and DataValueField for the dropdownlist

Public Class Color
    Private _c_olor As String
    Public Property c_olor() As String
        Get
            Return _c_olor
        End Get
        Set(ByVal value As String)
            _c_olor = value
        End Set
    End Property
End Class

My linq statment is

Dim colorDs = (From o In dt.Rows Select Color = New With {.c_olor = o("c_olor").ToString().Trim(Nothing).ToLower()}).Distinct().ToList()

However this never results in the distinct colors.

I have searched and searched for what I am looking for, and this seems to be one of the methods to produce a distinct set of results, but this and the others do not work.

My reasoning behind getting the colors this way, is that I need to get various other distinct values from the same UNION SELECT datasource, so would just do one DB call, cache the results, and then just used this cached datasource to retrieve all my distinct values.

Answer 1

I'm not familiar with Linq, but would it be an option to use your datasource as base and create distinct datatables on the fly? Have a look at the DataView.ToTable method. Here is another example.

Answer 2

I'm not a VB expert but it seems like you're creating your colors first and then trying to find the distinct colors

In this case, you need to implement Equals on your color class

Dim colorDs = (From o In dt.Rows Select Color = New With {.c_olor = o("c_olor").ToString().Trim(Nothing).ToLower()}).Distinct().ToList()

Alternatively, you could find disctinct values first and then create color instances

Dim colorDs = (From o In dt.Rows Select o("c_olor").ToString().Trim(Nothing).ToLower()).Distinct().ToList()

Should produce the list of unique string colors