CODEWITHSUNDEEP
vb.netlinq
BobNoobGuy
BobNoobGuy

Reputation: 1645

VB.net Linq join on two columns

Please help me. This is my first LINQ.

I have two datatables and I need to join on two fields.

How do I do this?

I tried the folowing but The join does not work. Error on "yk.Field(Of String)("secNo")" and the message is "Anonymous type member name can be inferred only from a simple or qualified name with no arguments"

    Dim dtYk As DataTable = New DataTable("dtYk")
    dtYk.Columns.Add("secNo")
    dtYk.Columns.Add("BomLine")
    dtYk.Rows.Add("EA(1)", 1)
    dtYk.Rows.Add("EA(8)", 17)

    Dim dtIvt As DataTable = New DataTable("dtIvt")
    dtIvt.Columns.Add("secNo")
    dtIvt.Columns.Add("BomLine")
    dtIvt.Columns.Add("PartCode")
    dtIvt.Rows.Add("EA(1)", 1, "TRML07-32-LH-ASSY**")
    dtIvt.Rows.Add("EA(8)", 17, "328")

    ' Create a DataSet. Put both tables in it.
    Dim dsYk As DataSet = New DataSet("dsYk")
    dsYk.Tables.Add(dtYk)

    Dim dsIvt As DataSet = New DataSet("dsIvt")
    dsIvt.Tables.Add(dtIvt)

    Dim LinQuery = From yk In dtYk.AsEnumerable
                   Join ivt In dtIvt.AsEnumerable
                   On New With {yk.Field(Of String)("secNo") , yk.Field(Of String)("BomLine")} Equals New With {ivt.Field(Of String)("secNo") , ivt.Field(Of String)("BomLine")}
                   Select New With {yk.Field(Of String)("secNo") , yk.Field(Of String)("BomLine"),ivt.Field(Of String)("secNo")}

this is a simple version that works. But this only joins on one field and select only one table.

Dim LinQuery = From yk In dtYk.AsEnumerable()
                       Join ivt In dtIvt.AsEnumerable()
                       On yk.Field(Of String)("secNo") Equals ivt.Field(Of String)("secNo")
                       Select yk

        Dim dt As New DataTable
        dt = LinQuery.CopyToDataTable

my goal is to join on two fields and the select should be yk.SecNo, yk.BomLine and ivt.PartCode. Please help. Thank you

Upvotes: 3

Views: 5225

Answers (3)

Mark
Mark

Reputation: 8150

@har07 shows that you can use And to join on a composite key, which is the best answer, but if you are wondering why the anonymous types didn't work, you need to use Key to mark which properties are used for equality checks, otherwise they are compared by reference. So, the following will work as well, but is less readable than the And version.

Dim LinQuery =
    From yk In dtYk.AsEnumerable()
    Join ivt In dtIvt.AsEnumerable() On
        New With { Key .secNo = yk.Field(Of String)("secNo"), Key .BomLine = yk.Field(Of String)("BomLine") } _
        Equals _
        New With { Key .secNo = ivt.Field(Of String)("secNo"), Key .BomLine = ivt.Field(Of String)("BomLine") }
    Select New With {
        .secNo = yk.Field(Of String)("secNo"),
        .BomLine = yk.Field(Of String)("BomLine"),
        .PartCode = ivt.Field(Of String)("PartCode")
    }

Upvotes: 1

har07
har07

Reputation: 89285

Using And keyword to join on composite key should work (refer to MSDN article, section "Perform a Join by Using a Composite Key"). Quick test that demonstrate this :

Dim dtYk As New DataTable()
dtYk.Columns.Add("secNo")
dtYk.Columns.Add("BomLine")
dtYk.Rows.Add(1, 3)
dtYk.Rows.Add(2, 4)
dtYk.Rows.Add(3, 5)

Dim dtIvt As New DataTable
dtIvt.Columns.Add("secNo")
dtIvt.Columns.Add("BomLine")
dtIvt.Rows.Add(1, 3)
dtIvt.Rows.Add(2, 5)
dtIvt.Rows.Add(3, 4)
Dim LinQuery = From yk In dtYk.AsEnumerable
               Join ivt In dtIvt.AsEnumerable
               On yk.Field(Of String)("secNo") Equals ivt.Field(Of String)("secNo") And
                  yk.Field(Of String)("BomLine") Equals ivt.Field(Of String)("BomLine")
               Select New With {.secNo1 = yk.Field(Of String)("secNo"),
                                 .bomLine = yk.Field(Of String)("BomLine"),
                                 .bomLine2 = ivt.Field(Of String)("BomLine")}
For Each o As Object In LinQuery
    'the output is only one row :'
    '{ secNo1 = 1, bomLine = 3, bomLine2 = 3 }'
    Console.WriteLine(o)
Next

And regarding error message you got, that means you need to manually specify member field name for the anonymous type (it's also demonstrated in Select clasue in above sample).

Upvotes: 2

user3910810
user3910810

Reputation: 234

Similar question to VB.Net LINQ - left outer join between two datatables - limit to one row

just ignore the one row bit.

Also you'll need to put this as your ON line to join on two fields

On yk.Field(Of String)("secNo") Equals ivt.Field(Of String)("secNo") And yk.Field(Of String)("BomLine") Equals ivt.Field(Of String)("BomLine")

Upvotes: 0

Related Questions