full outer join 3 DataTables using Linq in c#

Question

/*See last 3 lines of my question for my explanation of how my question is different than the suggested possible duplicate. */

I'm trying to join 3 DataTables together using Linq.

Let's say the DataTable names are table1, table2, and table3 Let's say each Datatable has 6 columns, and the column names are col1, col2, col3, col4, col5, and col6.

I want to join these tables where col1,col2,col3, and col4 match.

I want to append columns 5 and 6 from tables 2 and 3 to table1, resulting in a table4 that has 10 columns and all records whether matching or not (null if not matching).

I've researched this most of the morning and can't seem to wrap my head around it. I was going to put my attempt at the code below but I think all it would do is confuse people. Thank you in advance for any help. I hope I gave enough info.

EDIT: I'm going to put my attempt at the code below. I couldn't even get any join to work, much less all tables:

 var table4 = from one in Table1 join two in Table2 on one.col1 equals two.col1, one.col2 equals two.col2, one.col3 equals two.col3, one.col4 equals two.col4
                       select new { one.col1, two.col1, one.col2, two.col2, one.col3,two.col3,one.col4,two.col4};

my question is unique from the suggested duplicate due to the below: 1. that question addresses joining lists not DataTables 2. that question addresses joining based off matching one column not multiple

Possible duplicate of [LINQ - Full Outer Join](https://stackoverflow.com/questions/5489987/linq-full-outer-join) — mjwills, Dec 10 '17 at 21:42
@mjwills nope that was the first one I looked at, it's joining lists , also only joins on one column — Jar, Dec 10 '17 at 21:44
the second answer in the marked as duplicate has generic full outer join implementation that is also part of MoreLINQ https://stackoverflow.com/a/13503860/1383168 and can be used with DataTables and multiple keys — Slai, Dec 10 '17 at 22:53

Harald Coppoolse · Accepted Answer · 2017-12-12T07:57:25.743

I'm not sure whether you are using IEnumerable or IQueryable, the code will be similar.

A join on three tables is easier when written in query syntax, than when written in method syntax. See How to perform Join between multiple tables in LINQ lambda There you'll see how to write a join on three tables in method syntax.

IEnumerable<Table1Element> Table1 = ...
IEnumerable<Table2Element> Table2 = ...
IEnumerable<Table3Element> Table3 = ...

from table1Element in Table1
join table2Element in Table2
    on new
    {
        Col1 = table1Element.Col1,
        Col2 = table1Element.Col2,
        Col3 = table1Element.Col3,
        Col4 = table1Element.Col4,
    }
    equals new
    {
        Col1 = table2Element.Col1,
        Col2 = table2Element.Col2,
        Col3 = table2Element.Col3,
        Col4 = table2Element.Col4,
    }
join table3Element in Table3
    on new
    {
        Col1 = table2Element.Col1,
        Col2 = table2Element.Col2,
        Col3 = table2Element.Col3,
        Col4 = table2Element.Col4,
    }
    equals new
    {
        Col1 = table3Element.Col1,
        Col2 = table3Element.Col2,
        Col3 = table3Element.Col3,
        Col4 = table3Element.Col4,
    }
select new
{
    Col1 = table1Element.Col1,
    Col2 = table1Element.Col2,
    Col3 = table1Element.Col3,
    Col4 = table1Element.Col4,
    // only if required: col5 and col6 from table1
    FromTable1 = new
    {
         Col5 = table1Element.Col5,
         Col6 = table1Element.Col6,
    }
    FromTable2 = new
    {
         Col5 = table2Element.Col5,
         Col6 = table2Element.Col6,
    }
    FromTable3 = new
    {
         Col5 = table3Element.Col5,
         Col6 = table3Element.Col6,
    }
}

I use anonymous types here. One of the advantages of anonymous types is that they use equality by value instead of equality by references. So they are perfect to perform joins on multiple fields. See: the joy of anonymous types

If you do these joins quite often, consider creating a super class that contains Col1..Col4, or an interface. Don't forget to define value equality for this.

Addition after comment
One of the comments expressed confusion on the initialisation statements of Table1 / Table2 and Table3.

In your question you showed a code example where you used Table1, Table2, Table3. Apparently you had declared and initialized these variables. So I am certain the somewhere in your code you had:

var Table1 = ...

The only difference was that I wrote:

IEnumerable<Table1Element> Table1 = ...

I did this to make the rest of the code better readable. I defined the elements of Table1 to be of type Table1Element. I assumed that if your Table1 contains elements of MyClass you'd know what to replace in my code.

End of addition

this is extremely helpful. but what is "..." in the following part of your code: IEnumerable Table1 = ... — Jar, Dec 12 '17 at 03:35
I do not declare the 2 tables with a VAR, they are DataTables — Jar, Dec 17 '17 at 02:13
maybe I should do that instead of making datatables. I can't seem to figure this out — Jar, Dec 17 '17 at 02:14

full outer join 3 DataTables using Linq in c#

1 Answers1