How do I use Multiple Joins and Conditions using LINQ?

[Christhemist]

I have three DataTables and Three conditions in which I need to combine into one "Combined" DataTable:

Input:

AddressList
First
Last
Address
City
State
Zip
Offercode

Matrix1
Code
Offer1

Matrix2
Code2
Offer2

Output: (CombinedDataTable)
First
Last
Address
City
State
Zip
Offercode
Offer1
Offer2

My syntax for the these are not working in the "On" and "Concat" portions:

Dim both = From row1 In AddressListDatatable.AsEnumerable()
                       Join row2 In Matrix1Datatable.AsEnumerable()
                       Join row3 In Matrix2Datatable.AsEnumerable()
                   On row1.Field(Of String)("Offercode") Equals row2.Field(Of String)("Code") And
                   On row1.Field(Of String)("Offercode") Equals row3.Field(Of String)("Code2")
                       Select addressListColumnNames.Select(Function(alcn) row1(alcn)).Concat(matrixColumnNames.Select(Function(mcn) 
                       row2(mcn)))  row1(alcn)).Concat(matrix2ColumnNames.Select(Function(mcn2) row3(mcn2)))

For Each result In both
                CombinedDataTable.Rows.Add(result.ToArray())
            Next

[jmcilhinney]

Can you define "not working"?

[Christhemist]

I guess I just don't know where to start as far as syntax, I've gotten this far but I don't know if it will work because my Select statement isn't working:

Dim both = From row1 In AddressListDatatable.AsEnumerable()
                       Join row2 In GetMatrixFieldsDatatable.AsEnumerable()
                       On row1.Field(Of String)("Offercode") Equals row2.Field(Of String)("Code")
                       From row4 In AddressListDatatable.AsEnumerable()
                       Join row3 In GetMatrixFields2Datatable.AsEnumerable()
                       On row4.Field(Of String)("Offercode") Equals row3.Field(Of String)("Code2")
                       Select addressListColumnNames.Select(Function(alcn) row1(alcn)).Concat(matrix1ColumnNames.Select(Function(mcn) row2(mcn)))
            addressListColumnNames.Select(Function(alcn2) row4(alcn2)).Concat(matrix2ColumnNames.Select(Function(mcn2) row3(mcn2)))

I am not sure that using "row4" for the same addresslistDataTable is necessary...

[Christhemist]

Getting closer.... This join/on syntax appears to work, its the select statement that needs work still:

Dim both = From row1 In AddressListDatatable.AsEnumerable()
                       Join row2 In Matrix1Datatable.AsEnumerable()
                       On row1.Field(Of String)("Offercode") Equals row2.Field(Of String)("Code")
                       Join row3 In Matrix2Datatable.AsEnumerable()
                       On row1.Field(Of String)("Offercode") Equals row3.Field(Of String)("Code2")
                       Select addressListColumnNames.Select(Function(alcn) row1(alcn)).Concat(matrix1ColumnNames.Select(Function(mcn) row2(mcn))) row1(alcn2)).Concat(matrix2ColumnNames.Select(Function(mcn2) row3(mcn2)))

            For Each result In both

                CombinedDataTable.Rows.Add(result.ToArray())
            Next

[Christhemist]

I just had to add a second Concat after the first, see here:

Dim both = From row1 In AddressListDatatable.AsEnumerable()
                       Join row2 In Matrix1Datatable.AsEnumerable()
                       On row1.Field(Of String)("Offercode") Equals row2.Field(Of String)("Code")
                       Join row3 In Matrix2Datatable.AsEnumerable()
                       On row1.Field(Of String)("Offercode") Equals row3.Field(Of String)("Code2")
                       Select addressListColumnNames.Select(Function(alcn) row1(alcn)).Concat(matrix1ColumnNames.Select(Function(mcn) _
                       row2(mcn))).Concat(matrix2ColumnNames.Select(Function(mcn3) row3(mcn3)))

            For Each result In both

                CombinedDataTable.Rows.Add(result.ToArray())
            Next