VB.net Linq join on two columns

Question

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

har07 · Accepted Answer · 2014-08-08 02:29:16Z

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).

Mark · Accepted Answer · 2014-08-08 02:35:39Z

@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")
    }

Community · Accepted Answer · 2017-05-23 11:49:20Z

0

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")

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Aug 7, 2014 at 23:23

user3910810

2341 silver badge6 bronze badges

2 Comments

BobNoobGuy Over a year ago

I tried "on secno equals secno and bomline equals bomline" AND is not a correct syntax for LINQ. AND is not valid within JOIN I believe.

user3910810 Over a year ago

So it doesn't compile using an and in the on statement? I'd also help linq out a bit by defining the column types. e.g. Dim myCOl As New DataColumn("SecNo", System.Type.GetType("System.string")) dtYk.Columns.Add(myCOl)

Collectives™ on Stack Overflow

VB.net Linq join on two columns

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related