Using Left Outer Join in Linq

Question

I want to write one LINQ query with left outer join.For this I am able to write the corresponding SQL query which returns the desired output but the same in linq doesn't work for me.

This what my SQL looks like:

  SELECT Table1.ID, Table1.Description
  FROM Table1 LEFT OUTER JOIN
  Table2 ON Table1.AID = Table2.AID AND Table1.TID = Table2.TID
  WHERE (Table2.Status <> 'Using') OR (Table.Status IS NULL)

This query returns 7 records for me and that is what my requirement is.Now the same query I want to write with LINQ and this is what I tried with:

  return (from t1 in db.Table1.AsEnumerable()
    join t2 in db.Table2.AsEnumerable() on t1.AID equals t2.AID into outer
    from item in outer.DefaultIfEmpty()
    where item.TID == t1.TID
    && string.IsNullOrEmpty(item.Status) || item.Status != "Using"
    select t1
     );

But I have been facing issues with this.Here , it is not able to find item and thus returning

'Object reference not set to an instance of an object.'

What am I doing wrong over here????

I have been trying continuously but ended up with no solutions . So, any help in this would be highly appreciated.Thanks in advance..

[What is a NullReferenceException and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — Soner Gönül, Jan 08 '15 at 07:41
Why do you have this `AsEnumerable()`? You should be able to remove it. (twice) — Gert Arnold, Jan 08 '15 at 08:33

score 3 · Answer 1 · answered Jan 08 '15 at 07:46

3

Your SQL and LINQ statement are not the same, you have a different join clause. Try this:

from t1 in db.Table1
    join t2 in db.Table2 on new { 
                                aID = t1.AID, 
                                tID = t2.TID 
                                } 
                         equals new { 
                                aID = t2.AID, 
                                tID = t2.TID 
                                } into outer
    from item in outer.DefaultIfEmpty()
    where item.Status == null || item.Status != "Using"
    select t1

You can add multipe join relations with the new {} equals new {} statement.

answered Jan 08 '15 at 07:46

Loetn

3,832
25
41

Tried this but again getting the same error at `item.Status` part .While debugging it shows the value as null for that field.Can you please check. – Saroj Jan 08 '15 at 08:04
Thanks for your help.It worked after removing AsEnumerable with the same where condition. – Saroj Jan 08 '15 at 09:56

score 1 · Answer 2 · edited May 23 '17 at 10:33

1

DefaultIfEmpty returns a collection containing a single, default item if there is nothing in the collection.

As your entities are probably classes rather than structs, that means you get a collection with a single null in it, not a collection with a single item in it with null fields.

As well as the join clause change suggested by Loetn, you should also change your where condition to

where item == null || item.Status != "Using"

edited May 23 '17 at 10:33

Community

1
1

answered Jan 08 '15 at 08:48

Rawling

49,248
7
89
127

Thanks for your help.It worked after removing AsEnumerable with the same where condition. – Saroj Jan 08 '15 at 09:52
@Saroj That's because with the `AsEnumerable` you're using LINQ-to-Objects, where reading a property of a null reference will cause you an exception, while without the `AsEnumerable` you're using LINQ-to-SQL, and SQL doesn't care. – Rawling Jan 08 '15 at 09:54

Using Left Outer Join in Linq

2 Answers2