LINQ多重连接在多个条件下加入

本文是小编为大家收集整理的关于LINQ多重连接在多个条件下加入的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。

中文
English

问题描述

我有很少的大桌子，我需要加入它们.在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;

，但似乎执行计划有很大不同.个人资料说，它试图在没有条件的情况下加载所有表.

推荐答案

尝试将常数放在单独的条件下...

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;

本文地址：https://www.itbaoku.cn/post/1556972.html

百度翻译此文有道翻译此文

问题描述

I have few large tables and I need to join them. In SQL it looks like:

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

Tried to convert this query to a such 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;

But seems execution plan differs a lot. Profile says that it tries to load all tables without conditions..

推荐答案

Try putting the constant to a seperate condition...

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;

上一篇：选择有固定奖杯数量和积分的球员

下一篇：LINQ to Entities不承认Double Round(Double, Int32, System.MidpointRounding)方法。