linq连接查询返回null
问题描述:
我有三个表。
表Alinq连接查询返回null
id name des table2 table3
1 xyz TableA_des1 null 1
2 abc TableA_des2 1 2
3 hgd TableA_des2 2 3
表B
id name des Active
1 xyz TableB_des1 1
2 abc TableB_des2 1
3 hgd TableB_des2 1
表C
id name des Active
1 xyz TableC_des1 1
2 abc TableC_des2 1
3 hgd TableC_des2 1
LINQ查询
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})
的有关查询是给空。对调试变量res有空。
答
您应该避免将where
条件放在来自left outer join
右侧的范围变量上,因为这样可以有效地将它们转换为inner 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()
...
,或将它们联接(如果可能):
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()
...
为了理解为什么是,当bdata
是null
(即没有匹配的记录)时,尝试评估where bdata.Active == true
。实际上,如果这是LINQ to Objects,上述条件将生成NullReferenceException
。但是,LINQ to Entities可以处理那些不带任何例外的情况,因为数据库自然支持查询中的null
值,这些列通常是不可空的。因此,上面的简单计算结果为false
,因此将筛选结果记录并有效地消除left outer join
的影响,根据定义,应该返回左侧记录,而不管是否存在匹配的右侧记录。
这意味着,实际上有第三种方式操作(虽然前两个选项是优选) - 包括明确null
检查:
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
...
不返回'null'但一个空的集合。 Linq查询不返回'null'(除非你从中检索到可能为空的特定记录) –