Left outer join using LINQ - yields different outputs

Question

I am trying to write left outer join using LINQ. The SQL looks like,

SELECT  *                 
FROM Table1 td1
LEFT OUTER JOIN Table2
ON td1.ColumnName = td2.ColumnName
WHERE td2.ColumnName IS NULL                  
ORDER BY SomeColumns

If I run this query in SQL Query analyzer, it returns say 100 records. And my converted LINQ code returns 105 records. I have written LINQ in 2 ways as,

Method 1:

var data= (from td1in Table1
           join td2 in Table2.Where(a => a.ColumnName == (int?)null)
           on td1.ColumnName equals td2.ColumnName into outer
           from x in outer.DefaultIfEmpty()
           orderby SomeColumns
           select td1);

Method 2: This gives an exception as, failed to enumerate results

var data = from td1 in Table1
           join td2 in Table2
           on td1.ColumnName equals td2.ColumnName into outer
           from item in outer.DefaultIfEmpty()
           where item.ColumnName.Value == (int?)null
           orderby somecolumns
           select td1 ;

The column used in where clause is nullable int type.

The result returned in SQL analyzer seems to be correct one. Please help me in getting the identical results.

Thanks

gzaxx · Accepted Answer · 2013-07-10T11:42:00.197

1

Try this query:

var data = from td1 in Table1
           join td2 in Table2
           on td1.ColumnName equals td2.ColumnName into outer
           from item in outer.DefaultIfEmpty()
           where item == null
           orderby somecolumns
           select td1 ;

In your original query that line item.ColumnName.Value == (int?)null was wrong, because you tried to retrieve value for all ColumnName even if item was null. I corrected it and now it should work fine.

edited Jul 10 '13 at 11:42

answered Jul 10 '13 at 11:36

gzaxx

17,312
2
36
54

it throws an exception as, 'data.ToList()' threw an exception of type 'System.NullReferenceException' System.Collections.Generic.List {System.NullReferenceException} – VJOY Jul 10 '13 at 11:42
My mistake, fixed, try again please :) – gzaxx Jul 10 '13 at 11:43

Left outer join using LINQ - yields different outputs

1 Answers1