LINQ join 多了个 is null
我用的是Entity FrameWork 4.3
LINQ为  
var query = from v in
  (from r in this.DbContxt.Tab_Purchaselist
  join sr in this.DbContxt.Tab_Purchaseinfo
  on r.randid equals sr.randid into gc
  from g in gc.DefaultIfEmpty()
  select new
  {
  r.randid,
  r.purchaseOrderId,
  r.purchaseType,
  r.orderStatus,
  r.validEndDate,
  r.stockId,
  g.itemId,
  g.bookAmount
  }
  )
  where v.purchaseOrderId != null
  && v.validEndDate > currentDate && v.purchaseType == 0
  && v.stockId == dcId && orderStats.Contains(v.orderStatus)
  && itemIds.Contains(v.itemId)
  group v by v.itemId into g
  select new ItemOnLine
  {
  ItemId = g.Key,
  OnLineNum = g.Sum(r => r.bookAmount)
  };  

最后生成的SQL语句是

SELECT 1 AS C1, GroupBy1 . K1 AS itemId, GroupBy1 . A1 AS C2
  FROM (SELECT Extent2 . itemId AS K1, SUM(Extent2 . bookAmount) AS A1
  FROM tab_purchaselist AS Extent1
  LEFT OUTER JOIN tab_purchaseinfo AS Extent2
  ON (Extent1 . randid = Extent2 . randid)
  OR ((Extent1 . randid IS NULL) AND (Extent2 . randid IS NULL))
  WHERE (((((Extent1 . purchaseOrderId IS NOT NULL) AND
  (Extent1 . validEndDate > '2012-06-01 00:00:00')) AND
  (0 = (Extent1 . purchaseType))) AND
  (Extent1 . stockId = 10)) AND
  (((1 = Extent1 . orderStatus) OR (5 = Extent1 . orderStatus)) OR
  (((2 = Extent1 . orderStatus) OR
  (9 = Extent1 . orderStatus)) OR
  (10 = Extent1 . orderStatus))))  
  GROUP BY Extent2 . itemId) AS GroupBy1

在这个语句的 ON (Extent1 . randid = Extent2 . randid)
这个后面多了个 OR ((Extent1 . randid IS NULL) AND (Extent2 . randid IS NULL))
有什么办法去掉这个 or 因为这个or导致一个很奇怪的问题。所以想去掉

------解决方案--------------------
估计是gc.DefaultIfEmpty()引起的


------解决方案--------------------
你当前的是left join
换成 inner join 试试：


var query = from v in
(from r in this.DbContxt.Tab_Purchaselist
join sr in this.DbContxt.Tab_Purchaseinfo
on r.randid equals sr.randid
  
select new
{
r.randid,
r.purchaseOrderId,
r.purchaseType,
r.orderStatus,
r.validEndDate,
r.stockId,
sr.itemId,
sr.bookAmount
}
)
where v.purchaseOrderId != null
&& v.validEndDate > currentDate && v.purchaseType == 0
&& v.stockId == dcId && orderStats.Contains(v.orderStatus)
&& itemIds.Contains(v.itemId)
group v by v.itemId into g
select new ItemOnLine
{
ItemId = g.Key,