Linq To SQL - Left Outer Join

[josh.burwell]

i'm having troubles with a linq-to-sql statement and getting a left outer join to work. here is a sample of two tables i'm trying to join and my code that i've wrote.

Table1 Table2
f1 f2 f3 f4 fID f1 f2 f3 f4
val1 val2 val3 val4 1 val1 val2 val3 val4
val1 val2 val3 val5 2 val1 val2 val3 val5
val1 val2 val3 val6 3 val1 val2 val3 val6
val7 val8 val9 val10
val7 val8 val9 val11

Imports system.linq

Dim resultset = From tbl1 In Table1 _
Group Join tbl2 In Table2 _
On New With {tbl1.f1, tbl1.f2, tbl1.f3, tbl1.f4} _
Equals New With {.f1 = tbl2.f1, .f2 = tbl2.f2, .f3 = tbl2.f3, .f4 = tbl2.f4} Into tbl_join = Group _
From tbl2 In tbl_join.DefaultIfEmpty() _
Where tbl_join.Count = 0 _
Select tbl1.f1, _
tbl1.f2, _
tbl1.f3, _
tbl1.f4

I am expecting the result set to be...
f1 f2 f3 f4
val7 val8 val9 val10
val7 val8 val9 val11

Instead I'm returning ALL values from table 1. I need to return all values from the left outer join where tbl2.fID is null.

Thank you for your help!

 

[MattP]

If you're looking to get only the results where the right side of the join is DbNull just add a where clause checking to see if one of the fields is Nothing.

A quick test I threw together only returned the records you're looking for.

        Dim ctx As New JoinTestDataContext
        Dim result = From t1 In ctx.Table1s _
                     Group Join t2 In ctx.Table2s On t1.f1 Equals t2.f1 _
                        And t1.f2 Equals t2.f2 _
                        And t1.f3 Equals t2.f3 _
                        And t1.f4 Equals t2.f4 _
                     Into jn = Group _
                     From x In jn.DefaultIfEmpty _
                     Where x.f1 Is Nothing _
                     Select t1