[c#]LINQ 多加入多个条件

发布时间: 2017/2/26 19:57:40
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有几个大表,我需要加入他们的行列。在 SQL 中它看起来像︰

select * from dbo.Table1 t1
join dbo.Table1 t1Parent on t1.ParentId = t1Parent.Id
join dbo.MappingT1T3 t2 on t1Parent.Id = t2.ExternalId and t2.[Type] = 1
join dbo.Table3 t3 on t2.ForeignId = t3 .Id
where t1.[Type] = 3

试图将此查询转换为这种 LINQ:

from t1 in dbo.Table1
join t1Parent in dbo.Table1 on t1.ParentId equals t1Parent.Id
join t2 in dbo.MappingT1T3 on new { Id = t1Parent.Id, [Type] = (int)1 } equals new { Id = t2.ExternalId, [Type] = (int)t2.[Type] }
join t3 in dbo.Table3 on t2.ForeignId equals t3.Id
where t1.[Type] == 3;

但似乎是完全不同的执行计划。配置文件说它尝试加载没有条件的所有表。

解决方法 1:

请尝试将常数放到一个单独的条件......

from t1 in dbo.Table1
where t1.[Type] == 3 // <--- PUT THIS ONE HIGHER
join t1Parent in dbo.Table1 on t1.ParentId equals t1Parent.Id
join t2 in dbo.MappingT1T3 on t1Parent.Id equals Id = t2.ExternalId
where (int)t2.[Type] == 1 // <--- SEPARATE CONDITION
join t3 in dbo.Table3 on t2.ForeignId equals t3.Id;
赞助商