fROM：https://www.cnblogs.com/guyun/archive/2012/09/14/2684529.html
我昨天遇到一个LINQ下使用多条件比对产生LEFT JOIN的问题，经过深入研究，终于解决了，也让我学到了新的东西，特地拿来分享。
实例：有一张库存异常变更视图KCYD，仓库ID[Ckid]和物品ID[SpxxId]是该视图的唯一约束。有一张物品表ITEM，物品ID[ITEM_ID]是主键。还有一张表是统计正品和次品库存数量的视图SPKC，仓库ID[CKID]和物品ID[SPXXID]是该视图的唯一约束。现在的要求是根据条件查询库存异常变更的物品信息，即要求KCYD左联ITEM再左联SPKC。KCYD和ITEM的公共字段是物品ID，KCYD和SPKC的公共字段是仓库ID和物品ID。我原先想到的写法如下： var query = from k in DBContext.KCYD join i in DBContext.ITEM on k.SPXXID equals i.ITEM_ID into g from gc in g.DefaultIfEmpty() join s in DBContext.SPKC on k.SpxxId equals s.SPXXID into g1 from gc1 in g1.DefaultIfEmpty() where k.Ckid == gc1.CKID select new { ... ... };但是生成的SQL语句，KCYD和ITEM表是LEFT OUTER JOIN的，但是联SPKC表却变成了INNER JOIN，这是为啥呢，经过一番折腾下来，发现问题出在where k.Ckid == gc1.CKID，如果把这个条件去掉的话，那就成了LEFT OUTER JOIN了，然后我就在想这个条件应该放在哪呢，LINQ里面到底支不支持联合主键的问题呢，在网上搜了半天，发现可以用 on new {字段1,字段2} equals new {字段1,字段2} into g的方法，于是修改代码如下： var query = from k in DBContext.KCYD join i in DBContext.ITEM on k.SPXXID equals i.ITEM_ID into g from gc in g.DefaultIfEmpty() join s in DBContext.SPKC on new {k.SpxxId,k.Ckid} equals new {s.SPXXID,s.CKID} into g1 from gc1 in g1.DefaultIfEmpty() select new { ... ... };但是很不给力的是这样居然提示错误：The type arguments cannot be inferred from the query.简直就是杯具，难道LINQ不支持这样搞？唉，在我绝望的时候同事为我看出了端倪，原来equals两边的参数字段名的大小写必须完全匹配。即完整代码如下： var query = from k in DBContext.KCYD join i in DBContext.ITEM on k.SPXXID equals i.ITEM_ID into g from gc in g.DefaultIfEmpty() join s in DBContext.SPKC on new {SPXXID=k.SpxxId,CKID=k.Ckid} equals new {s.SPXXID,s.CKID} into g1 from gc1 in g1.DefaultIfEmpty() select new { ... ... };大功告成!
例子：
list = from goods in this.CurrentDLL.LoadEntities(a => true)
join goodsCategroy in GetCurrentDbSession.Tbl_OfficeSupplies_GoodsCategoryDLL.LoadEntities(a => true) on goods.GoodsCategoryID equals goodsCategroy.ID into tmp from tt in tmp.DefaultIfEmpty() join goodsUnit in GetCurrentDbSession.Tbl_OfficeSupplies_GoodsUnitDLL.LoadEntities(a => true) on goods.UnitID equals goodsUnit.ID into tmp1 from tt1 in tmp1.DefaultIfEmpty() join goodsSpecifications in GetCurrentDbSession.Tbl_OfficeSupplies_GoodsSpecificationDLL.LoadEntities(a => true) on goods.ID equals goodsSpecifications.GoodsID into tmp2 from tt2 in tmp2.DefaultIfEmpty() join goodsStore in this.GetCurrentDbSession.Tbl_OfficeSupplies_GoodsStoreDLL.LoadEntities(a=>true) on new { GoodsID= goods.ID, GoodsSpecificationID= tt2.ID } equals new { goodsStore.GoodsID, goodsStore.GoodsSpecificationID } into tmp3 from tt3 in tmp3.DefaultIfEmpty()where tt.ID == new Guid(goodsCategoryID)
select new { GoodsID = goods.ID, GoodsName = goods.Name, GoodsCategoryName = tt.Name, GoodsSpecificationID = tt2.ID, GoodsSpecificationName = tt2.Name, GoodsUnitName = tt1.Name, ProcurementApplyCount = "", ProcurementPricePer = "", StoreCount = tt3.Count };
修改为：
from goods in this.CurrentDLL.LoadEntities(a => true)
join goodsCategroy in GetCurrentDbSession.Tbl_OfficeSupplies_GoodsCategoryDLL.LoadEntities(a => true) on goods.GoodsCategoryID equals goodsCategroy.ID into tmp from tt in tmp.DefaultIfEmpty() join goodsUnit in GetCurrentDbSession.Tbl_OfficeSupplies_GoodsUnitDLL.LoadEntities(a => true) on goods.UnitID equals goodsUnit.ID into tmp1 from tt1 in tmp1.DefaultIfEmpty() join goodsSpecifications in GetCurrentDbSession.Tbl_OfficeSupplies_GoodsSpecificationDLL.LoadEntities(a => true) on goods.ID equals goodsSpecifications.GoodsID into tmp2 from tt2 in tmp2.DefaultIfEmpty() join goodsStore in this.GetCurrentDbSession.Tbl_OfficeSupplies_GoodsStoreDLL.LoadEntities(a=>true) on new { GoodsID= (Guid?)goods.ID, GoodsSpecificationID= (Guid?)tt2.ID } equals new { goodsStore.GoodsID, goodsStore.GoodsSpecificationID } into tmp3 from tt3 in tmp3.DefaultIfEmpty()where tt.ID == new Guid(goodsCategoryID)
select new { GoodsID = goods.ID, GoodsName = goods.Name, GoodsCategoryName = tt.Name, GoodsSpecificationID = tt2.ID, GoodsSpecificationName = tt2.Name, GoodsUnitName = tt1.Name, ProcurementApplyCount = "", ProcurementPricePer = "", StoreCount = tt3.Count };条件中数据类型必须一致