LINQ 左联接查询

本文关键字：查询 LINQ | 更新日期: 2023-09-27 18:37:04

>我正在尝试将sql查询转换为LINQ，但是写入查询后无法从结果集中获取记录

SELECT T.ServiceOrderNo,T.STATUS, T.SubStatus,T.orderVersion,T.OrderDate
  ,@pid, T.EventID, 'FOI'    
 FROM #temp1 T     
 LEFT JOIN Tbl_Service_Order_Progress O  ON T.ServiceOrderNo  DATABASE_DEFAULT = O.ServiceOrderNo 
  AND O.PARENTID = @pid    
  AND O.ServiceOrderType  = 'FOI'    
 WHERE O.ServiceOrderNo  IS NULL

上面的查询下面我正在尝试在 LINQ 中

var lstInsertFOI = (from i in lstFOI
                    join j in lstSOP on i.fulfilmentOrderItemIdentifier equals j.ServiceOrderNo into res
                                from subRight in res.DefaultIfEmpty()
                                where subRight.ParentId == parentId && subRight.ServiceOrderNo == null && subRight.ServiceOrderType.Equals("FOI")
                                select new
                                {
                                    ServiceOrderNo = subRight.ServiceOrderNo == null ? i.fulfilmentOrderItemIdentifier : subRight.ServiceOrderNo,
                                    EventStatus = i.status,
                                    EventSubStatus = i.subStatus,
                                    OrderVersion = i.orderVersion,
                                    EVENTRECEIVEDDATE = i.orderDate,
                                    ParentId = parentId,
                                    EventID = i.eventID,
                                    ServiceOrderType = "FOI",                                    
                                }).ToList();

上面的 linq 查询不会获取预期结果，它应该从 lstFOI 列表中返回记录数，但不返回任何记录。linq 查询是否正确？

LINQ 左联接查询

让我们从SQL查询开始。

LEFT JOIN Tbl_Service_Order_Progress O
ON T.ServiceOrderNo = O.ServiceOrderNo 
    AND O.PARENTID = @pid AND O.ServiceOrderType  = 'FOI'

相当于

LEFT JOIN (SELECT * FROM Tbl_Service_Order_Progress 
    WHERE PARENTID = @pid AND ServiceOrderType  = 'FOI') O
ON T.ServiceOrderNo = O.ServiceOrderNo

然后

WHERE O.ServiceOrderNo  IS NULL

意味着查询实际上使用的是反联接，即包括左侧没有来自右侧的匹配记录的所有记录。

考虑到所有这些，等效的 LINQ 查询应如下所示：

var lstInsertFOI = (
    from i in lstFOI
    join j in lstSOP
        .Where(e => e.ParentId == parentId && subRight.ServiceOrderType == "FOI")
    on i.fulfilmentOrderItemIdentifier equals j.ServiceOrderNo into res
    where !res.Any()
    select new
    {
        ServiceOrderNo = i.fulfilmentOrderItemIdentifier,
        EventStatus = i.status,
        EventSubStatus = i.subStatus,
        OrderVersion = i.orderVersion,
        EVENTRECEIVEDDATE = i.orderDate,
        ParentId = parentId,
        EventID = i.eventID,
        ServiceOrderType = "FOI",                                    
    }).ToList();
相关文章：