CODEWITHSUNDEEP
entity-frameworklinqasp.net-mvc-5
anand
anand

Reputation: 1579

LINQ join query returning null

I've three tables.

Table A

id   name   des           table2     table3
1    xyz    TableA_des1    null      1
2    abc    TableA_des2    1         2
3    hgd    TableA_des2    2         3

Table B

id   name   des           Active
 1    xyz    TableB_des1   1 
 2    abc    TableB_des2   1 
 3    hgd    TableB_des2   1

Table C

id   name   des           Active
 1    xyz    TableC_des1    1
 2    abc    TableC_des2    1
 3    hgd    TableC_des2    1

LINQ Query

var res =    (from a in TableA
              where id = 1

              join b in TableB on a.table2 equals b.id into ab
              from bdata in ab.DefaultIfEmpty()
              where bdata.Active = true

              join c in TableC on a.table3 equals c.id into ac
              from cdata in ac.DefaultIfEmpty()
              where cdata.Active = true

              select new { data1 = a.name, data2 = bdata?? string.Empty, data3 = cdata?? string.Empty})

The about query is giving null. On debugging variable res has null.

Upvotes: 1

Views: 3476

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205629

You should avoid putting where conditions on range variables coming from the right side of a left outer join, because doing so effectively turns them into inner join.

Instead, you should either apply the right side filtering before the join:

from a in TableA
where id = 1

join b in TableB.Where(x => a.Active)
on a.table2 equals b.id
into ab
from bdata in ab.DefaultIfEmpty()

join c in TableC.Where(x => x.Active)
on a.table3 equals c.id
into ac
from cdata in ac.DefaultIfEmpty()

...

or include them in the join (when possible):

from a in TableA
where id = 1

join b in TableB
on new { id = a.table2, Active = true } equals new { b.id, b.Active }
into ab
from bdata in ab.DefaultIfEmpty()

join c in TableC
on new { id = a.table3, Active = true } equals new { c.id, c.Active }
into ac
from cdata in ac.DefaultIfEmpty()

...

In order to understand why is that, try to evaluate where bdata.Active == true when bdata is null (i.e. there is no matching record). Actually if this was LINQ to Objects, the above criteria will generate NullReferenceException. But LINQ to Entities can handle that w/o exceptions, since databases naturally support null values in queries for a columns which are normally non nullable. So the above simple evaluates to false, hence is filtering the resulting record and effectively removing the effect of a left outer join which by definition should return the left side record regardless of whether a matching right side record exists.

Which means that actually there is a third way (althought the first two options are preferable) - include an explicit null checks:

from a in TableA
where id = 1

join b in TableB
on a.table2 equals b.id
into ab
from bdata in ab.DefaultIfEmpty()
where bdata == null || bdata.Active

join c in TableC
on a.table3 equals c.id
into ac
from cdata in ac.DefaultIfEmpty()
where cdata == null || cdata.Active

...

Upvotes: 4

Related Questions