Left outer join using LINQ - yields different outputs

Couple of days ago, I was working on a project where I was supposed to convert SQL queries in LINQ. There was a simple SQL query as;

1
2
3
4
5
SELECT  * FROM Table1 td1LEFT
OUTER JOIN Table2ON 
td1.ColumnName = td2.ColumnName
WHERE td2.ColumnName IS NULL
ORDER BY SomeColumns

I ran this query in SQL Query analyzer, it returned me 100 records. And my converted LINQ code returned me 105 records. I converted this query intto LINQ to perform Left Outer Join as;
I did this in 2 ways;
Method 1:

1
2
3
4
5
6
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: 
1
2
3
4
5
6
7
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 ;

This gave me an exception as, failed to enumerate results 

1
2
3
4
5
6
7
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 my original query that line item.ColumnName.Value == (int?)null was wrong, because I tried to retrieve value for all ColumnName even if item was null. I corrected that with the following query and it worked fine.

No comments:

Post a Comment