none
LINQ to Dataset - trouble with Distinct() RRS feed

  • Question

  • I'm a bit new to LINQ and am having trouble tackling the Distinct() clause.

     

    I have a DataTable containing employee information for which I need to need to extract all distinct combinations of division ID and division name.  The table is denormalized with [DivisionID] being the primary key and [Division] containing the division name.  For simplicity let's assume the table structure is as follows:

     

    CREATE TABLE Employees

    (

    LastName nvarchar(50),

    FirstName nvarchar(50),

    DivisionID int,

    Division nvarchar(50)

    )

     

    The code in my app is as follows:

     

    Dim dt As DataTable = CodeThatCreatesDataTable()

     

    Dim query = (From div In dt.AsEnumerable() _

                 Order By div.Field(Of String)("Division") _

                 Select New With { _

    .DivisionID = div.Field(Of Integer)("DivisionID"), _

    .Division = div.Field(Of String)("Division")}).Distinct()

     

    The result of executing the query is the same with or without the Distinct() clause added - duplicates are returned from the query.  I'm assuming I need to pass something of the IEqualityComparer(Of T) variety to the Distinct() method, but am uncertain as to the correct syntax.  I greatly appreciate any help.

    Thursday, July 17, 2008 8:24 PM

All replies

  • (I can try and answer this question, but you might want to post it in the ADO.NET/DataSet forum.)

     

    I think you'll need to use the GroupBy clause to extract all distinct combinations of division ID and division name.

     

    Hope that helps,

     

    --Samir

     

    Friday, July 18, 2008 5:00 PM
  • (Oddly, a moderator moved this post from the Dataset forum to here)

     

    That would likely work.  Again, I'm stuck on the syntax for the Group By clause in the query expression.  Trivial from a SQL standpoint, but LINQ doesn't like me using div.Field(xxx) in the Group By.

    Monday, July 21, 2008 1:44 PM
  • I don't know if this is what you're seeing or not, but Distinct will operate on both fields, ex:

    DivisionID: 1
    Division: Sample

    DivisionID: 2
    Division: Sample

    So both records would be returned by your query (with or without distinct).
    Monday, July 21, 2008 6:04 PM
  • I'm with you there.

     

    For example, I have:

     

    DivisionID: 1

    Division: NY

     

    DivisionID: 2

    Division: CT

     

    DivisionID: 3

    Division: FL

     

    The output I see from the LINQ statement is:

     

    DivisionID - Division

    1 - NY

    1 - NY

    2 - CT

    2 - CT

    2 - CT

    2 - CT

    3 - FL

    3 - FL

    3 - FL

     

    What I'm looking for is the distinct list (those three division with their name and ID):

     

    DivisionID - Division

    1 - NY

    2 - CT

    3 - FL

     

     

    Monday, July 21, 2008 6:53 PM
  • Strange, that should work (at least, it worked OK in my test code). How are you checking the results? Through the debugger (viewing the Results property of the LINQ query?), or some other way? Is it possible you're not viewing the results of the query but something else?
    Monday, July 21, 2008 8:31 PM
  • Initially, I was viewing the DataSet through an ASP.NET GridView.  The LINQ expression was meant to drive a DropDownList:

     

    drpFilterDivision.DataSource = query

    drpFilterDivision.DataTextField = "Division"

    drpFilterDivision.DataValueField = "DivisionID"

    drpFilterDivision.DataBind()

     

    Afterwards, I built a small console app with a foreach loop to display the results of the expression.

    Tuesday, July 22, 2008 12:35 PM
  • OK, one other thought - do you happen to have leading and/or trailing spaces for some of those entries that might make them appear as distinct items?
    Tuesday, July 22, 2008 3:37 PM