Join Multiple Datatables in LINQ using Group Join

Question

I have five datatablas in one dataset. Now I need to join them all together. The point is, that some entries from table1 not have an entry in the other tables. So I need a left join or left outer join. In SQL it works like this:

SELECT *
FROM TABLE1 LDC 
LEFT JOIN TABLE2 LGDE ON LDC.KEY1 = LGDE.KEY2
LEFT JOIN TABLE3 DEFI ON  LGDE.KEY3 = DEFI.KEY 4
LEFT JOIN TABLE4 SETT ON DEFI.KEY5 = SETT.KEY6
LEFT JOIN TABLE5 LST ON SETT.KEY7 = LST.KEY8

Now I rebuilt that statement with LINQ in my VB.NET code. In some other posts I figured out, that I have to use DefaultIfEmpty(). But I could not get this to work. So here's my Code. (We're on the .NET Framework 3.5)

Dim result = From ldc In TABLE1
               Group Join lgde1 In TABLE2 On ldc.Field(Of String)("KEY1") Equals lgde1.Field(Of String)("KEY2") Into g = Group
             From lgde In g.DefaultIfEmpty()
               Group Join defi1 In TABLE3 On lgde.Field(Of Decimal?)("KEY3") Equals defi1.Field(Of Decimal?)("KEY4") Into g2 = Group
             From defi In g2.DefaultIfEmpty()
               Group Join sett1 In TABLE4 On defi.Field(Of Decimal?)("KEY5") Equals sett1.Field(Of Decimal?)("KEY6") Into g3 = Group
             From sett In g3.DefaultIfEmpty()
               Group Join lst1 In TABLE5 On sett.Field(Of Decimal?)("KEY7") Equals lst1.Field(Of Decimal?)("KEY8") Into g4 = Group
             From lst In g4.DefaultIfEmpty()

(The Select Part works, thats why I did not post it)

I always get the following error:

The value mustn't be NULL. Parametername: row in System.Data.DataRowExensions.Field[T](Datarow, row, String columnName) on Line 336.

Any idea why it's not working?

Regards

score 0 · Answer 1 · edited May 23 '17 at 12:07

0

It looks like you are missing the additional info required for DefaultIfEmpty(), when using it for a left outer join.

See this example, and this duplicate question.

edited May 23 '17 at 12:07

Community

1
1

answered Nov 30 '15 at 14:26

Sam Makin

1,526
8
23

T.S. · Accepted Answer · 2015-12-01T05:30:02.153

I found Group Join perform worse than following syntax

Dim queryResult = _
    From t1 In Table1
    From t2 In Table2.Where(Function(r2) r2.Id = t1.Id).DefaultIfEmpty()
    From t3 In Table3.Where(Function(r3) r3.Id2 = t2.Id2).DefaultIfEmpty()
    . . . .

But then, unless you select t1 (we don't see your select part), you would probably need to provide something in DefaultIfEmpty

.DefaultIfEmpty(New MyObject() With { . . . . })

Because if you don't, you have this error that you're getting. And this is the answer why you getting this error. You getting it because you trying to use a value that doesn't exist as result of left join. This is similar to DB join, when you check if column value is Null

That's it! Thank you very much :) – tobiwan Dec 03 '15 at 13:28 — tobiwan, Dec 03 '15 at 13:28

Join Multiple Datatables in LINQ using Group Join

2 Answers2