LINQ join 多了个 is null

[kingson8811]

我用的是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导致一个很奇怪的问题。所以想去掉

[EnForGrass]

估计是gc.DefaultIfEmpty()引起的

[q107770540]

你当前的是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,
OnLineNum = g.Sum(r => r.bookAmount)
};

[kingson8811]

自己解决了。给后人留个解决方法。是因为在Model里面定义了randid 这个字段可以为null.在model里面加个不能为空的验证就行了