Join two DataTables even if foreign key is NULL

Question

I have 2 DataTables in my application. The first DataTable is called Table1 and looks like this

-------------------------------------
| Key | Column1 | Column2 | Foreign |
|-----------------------------------|
|  0  | dsfsfsd | sdfsrer |    1    |
|-----------------------------------|
|  1  | dertert | qweqweq |   NULL  |
|-----------------------------------|
|  2  | prwersd | xzcsdfw |    3    |
-------------------------------------

And the second is called Table2 and looks like this

----------------------------------------
|  Key   | Column3 | Column4 | Column5 |
|--------------------------------------|
|   1    | dsfsfsd | sdfsrer | fghfghg |
|--------------------------------------|
|   3    | prwersd | xzcsdfw | nbmkuyy |
----------------------------------------

So I want to do an inner join on these two tables using LINQ so that the joined table looks like this. I do not want to lose the data from Table1 if the foreign key that links to Table2 is NULL

---------------------------------------------------------
| Key | Column1 | Column2 | Column3 | Column4 | Column5 |
|-------------------------------------------------------|
|  0  | dsfsfsd | sdfsrer | dsfsfsd | sdfsrer | fghfghg |
|-------------------------------------------------------|
|  1  | dertert | qweqweq |  NULL   |  NULL   |  NULL   | // This row is missing in my application
|-------------------------------------------------------|
|  2  | prwersd | xzcsdfw | prwersd | xzcsdfw | nbmkuyy |
---------------------------------------------------------

Here is what I have tried

var query = from table1Row in Table1.AsEnumerable()
            join table2Row in Table2.AsEnumerable()
                on table1Row.Foreign equals table2Row.Key
            select new SelectedColumns
            {
                Column1 = table1Row.Column1
                Column2 = table1Row.Column2
                Column3 = table2Row.Column3
                Column4 = table2Row.Column4
                Column5 = table2Row.Column5
            }

However, this LINQ query skips the record that doesn't have a match. What can I do to get the result above?

Solution: So C.J.'s answer pointed me in the right direction. I changed the query to

var query = from table1Row in Table1.AsEnumerable()
            join table2Row in Table2.AsEnumerable()
                on table1Row.Foreign equals table2Row.Key into leftJoin
            from table2Row in leftJoin.DefaultIfEmpty()
            select new SelectedColumns
            {
                Column1 = table1Row.Column1
                Column2 = table1Row.Column2
                Column3 = table2Row.Column3
                Column4 = table2Row.Column4
                Column5 = table2Row.Column5
            }

However, now it was throwing the exception Value cannot be null. Parameter name: row. It turns out that when you are selecting the fields you would like, you need to use a boolean expression to check if the value is NULL. So I updated that section to

Column1 = table1Row.Field<COLUMN_TYPE?>("COLUMN_TYPE")
Column2 = table1Row.Field<COLUMN_TYPE?>("Column2")
Column3 = table2Row == null ? (COLUMN_TYPE?)null : table2Row.Field<COLUMN_TYPE?>("Column3")
Column4 = table2Row == null ? (COLUMN_TYPE?)null : table2Row.Field<COLUMN_TYPE?>("Column4")
Column5 = table2Row == null ? (COLUMN_TYPE?)null : table2Row.Field<COLUMN_TYPE?>("Column5")

And it worked. Make sure you update COLUMN_TYPE to that specific column's type. The objects in SelectedColumns are also all Nullable<>

possible duplicate of [LEFT OUTER JOIN in LINQ](http://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — Fredou, Jul 23 '14 at 17:40
possible duplicate of [this](http://stackoverflow.com/questions/700523/linq-to-sql-left-outer-join) — Farhad Alizadeh Noori, Jul 23 '14 at 17:47

score 7 · Accepted Answer · answered Jul 23 '14 at 17:40

7

You need to use the Left Outer Join in LINQ

Please refer to this page: http://msdn.microsoft.com/en-US/vstudio/ee908647.aspx#leftouterjoin

answered Jul 23 '14 at 17:40

C.J.

3,409
8
34
51

This pointed me in the right direction. I updated my question with what I did to get it working for me – TFischer Jul 23 '14 at 19:30
Updated link: https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins – Patrick Koorevaar May 17 '22 at 17:53

Join two DataTables even if foreign key is NULL

1 Answers1