none
Join multiple columns between two tables RRS feed

  • Question

  • I'm trying to join multiple columns between two tables, but my code allways returns zero result rows. What am I doing wrong? This is my code:

    Dim result = _
          From Table0 In dsData.Tables(0).AsEnumerable _
          Join Table1 In dsData.Tables(1) On New With { _
                                                                .criteria1 = Table0.Field(Of String)("Id"), _
                                                                .criteria2 = Table0.Field(Of String)("Text") _
                                                                } Equals New With { _
                                                                    .criteria1 = Table1.Field(Of String)("Id"), _
                                                                    .criteria2 = Table1.Field(Of String)("Text") _
                                                                    }

    My second question is, is it possible to do the joining dynamicly? My target is that I only have to pass two Arrays with the columnnames and the method joins all of them between the two tables. Is this possible?
    Thursday, September 11, 2008 1:50 PM

Answers

  • Your particular query is doing object reference comparision between 2 different instances of an anonymous class.

    http://msdn.microsoft.com/en-us/library/bb531340.aspx (Join Clause in Visual Basic)

     

    Writing your query something like the following my work better.

    Dim result = _

        From Table0 In dsData.Tables(0).AsEnumerable() _

        Join Table1 In dsData.Tables(1).AsEnumerable() On _

        Table0.Field(Of String)("Id") Equals Table1.Field(Of String)("Id") And _

        Table0.Field(Of String)("Text") Equals Table1.Field(Of String)("Text") _

        Select Table0

     

    Saturday, September 20, 2008 12:47 AM
    Moderator

All replies

  • Your particular query is doing object reference comparision between 2 different instances of an anonymous class.

    http://msdn.microsoft.com/en-us/library/bb531340.aspx (Join Clause in Visual Basic)

     

    Writing your query something like the following my work better.

    Dim result = _

        From Table0 In dsData.Tables(0).AsEnumerable() _

        Join Table1 In dsData.Tables(1).AsEnumerable() On _

        Table0.Field(Of String)("Id") Equals Table1.Field(Of String)("Id") And _

        Table0.Field(Of String)("Text") Equals Table1.Field(Of String)("Text") _

        Select Table0

     

    Saturday, September 20, 2008 12:47 AM
    Moderator
  • As Andy (http://blogs.msdn.com/aconrad/) has brought to my attention, this may actually be a problem with the VB compiler.  The same example actually does work in C# as you'd expect.

     

    With VB the anonymous types used in the join did not have an Equals method implemented resulting in object reference comparision between two objects of the same type.  However in C#, the compiler would generate the Equals method resulting in your expected behavior.

     

    var result = from a in t1.AsEnumerable()

    join b in t2.AsEnumerable() on new

    {

    criteria1 = a.Field<string>("Id"),

    criteria2 = a.Field<string>("Text"),

    } equals new

    {

    criteria1 = b.Field<string>("ID"),

    criteria2 = b.Field<string>("Text")

    }

    select new

     {

     a,

     b

     };

    Monday, September 22, 2008 5:45 PM
    Moderator