如何在EF Core中用C#实现含左外连接的SQL插入逻辑

我需要将一段SQL插入逻辑转换为EF Core的C#代码。现有自定义类列表myCpRegs，它来自函数[fn_TrackingFromCpReg_CpRegPos] (@dtFrom, @dtTo)，要实现包含左外连接的SQL逻辑，原始SQL如下：

insert into TrackingHistoryPos (TrackingHistoryID, Obj_ContID, Obj_TrayConcID, Obj_ProdID, Obj_CostCentreID, HasSN, Qty,
            Parent_ContID, StockLocationIDFrom, StockLocationID, 
            IsDeleted, TrackTime, IsPreliminary, IsPhysical)
    select
        B.TrackingHistoryID, A.Obj_ContID, A.Obj_TrayConcID, A.Obj_ProdID, A.Obj_CostCentreID, A.HasSN, A.Qty,
        A.Parent_ContID, A.StockLocationIDFrom, A.StockLocationID, 
        A.IsDeleted, A.TrackTime, A.IsPreliminary, A.IsPhysical
    from 
        [fn_TrackingFromCpReg_CpRegPos] (@dtFrom, @dtTo) A
    left outer join 
        TrackingHistory B on A.CpRegID = B.CpRegID
    order by 
        A.CpRegPosID

我尝试了以下两种实现方式，但均出现报错：

第一种尝试代码：

trackingHistoryPos = myCpRegs
    .GroupJoin(trackings, c => c.CpRegID, t => t.CpRegID
    myCpRegs.Where(x => (x.Pos_Obj_TrayConcID ?? x.Pos_Obj_ContID) is not null)
    .Select(x => new TrackingHistoryPosRow()
    {
        TrackingHistoryID = , // TODO
        Obj_ContID = x.Pos_Obj_ContID,
        Obj_TrayConcID = x.Pos_Obj_TrayConcID,
        Obj_ProdID = x.Pos_Obj_ProdID,
        Obj_CostCentreID = , //TODO
        HasSN = x.Pos_Obj_TrayConcID is not null && (x.Pos_Obj_TrayHasSN ?? false),
        Qty = x.Pos_Qty,
        Parent_ContID = , //TODO
        StockLocationIDFrom = , //TODO
        StockLocationID = , //TODO
        IsDeleted = x.IsRejected == 1,
        TrackTime = x.TrackTime,
        IsPreliminary = x.IsPreliminary == 1,
        IsPhysical = x.IsPhysical == 1,
    })
    ).ToList();

第二种尝试代码：

var query = from c in myCpRegs
            join t in trackingsHistory on c.CpRegID equals t.CpRegID into ps_jointable
            from ct in ps_jointable.DefaultIfEmpty()
            orderby c.Pos_CpRegPosID
            select new TrackingHistoryPosRow()
            {
                TrackingHistoryID = ct.TrackingHistoryID, // TODO
                Obj_ContID = c.Pos_Obj_ContID,
                Obj_TrayConcID = c.Pos_Obj_TrayConcID,
                Obj_ProdID = c.Pos_Obj_ProdID,
                //Obj_CostCentreID = , //TODO
                HasSN = c.Pos_Obj_TrayConcID is not null && (c.Pos_Obj_TrayHasSN ?? false),
                Qty = c.Pos_Qty,
                //Parent_ContID = , //TODO
                //StockLocationIDFrom = , //TODO
                //StockLocationID = , //TODO
                IsDeleted = c.IsRejected == 1,
                TrackTime = c.TrackTime,
                IsPreliminary = c.IsPreliminary == 1,
                IsPhysical = c.IsPhysical == 1,
            };

trackingHistoryPos = query.ToList();

正确的EF Core实现方式 #

1. 查询语法（贴近原始SQL结构，推荐）

第二种尝试的方向正确，只需补全字段、处理空值并添加过滤条件：

var query = from c in myCpRegs
            // 左外连接TrackingHistory，通过into分组+DefaultIfEmpty()实现左连接逻辑
            join t in trackingsHistory on c.CpRegID equals t.CpRegID into joinedTrackings
            from ct in joinedTrackings.DefaultIfEmpty()
            // 对应原始SQL的过滤条件：保留Pos_Obj_TrayConcID或Pos_Obj_ContID不为空的记录
            where (c.Pos_Obj_TrayConcID ?? c.Pos_Obj_ContID) != null
            // 对应原始SQL的排序规则
            orderby c.Pos_CpRegPosID
            select new TrackingHistoryPosRow()
            {
                // 左连接可能无匹配项，用空条件运算符避免空引用异常
                TrackingHistoryID = ct?.TrackingHistoryID,
                Obj_ContID = c.Pos_Obj_ContID,
                Obj_TrayConcID = c.Pos_Obj_TrayConcID,
                Obj_ProdID = c.Pos_Obj_ProdID,
                // 补全原始SQL中A表对应的字段
                Obj_CostCentreID = c.Obj_CostCentreID,
                HasSN = c.Pos_Obj_TrayConcID != null && (c.Pos_Obj_TrayHasSN ?? false),
                Qty = c.Pos_Qty,
                Parent_ContID = c.Parent_ContID,
                StockLocationIDFrom = c.StockLocationIDFrom,
                StockLocationID = c.StockLocationID,
                IsDeleted = c.IsRejected == 1,
                TrackTime = c.TrackTime,
                IsPreliminary = c.IsPreliminary == 1,
                IsPhysical = c.IsPhysical == 1,
            };

trackingHistoryPos = query.ToList();

2. 方法语法（链式调用风格）

修正第一种尝试的语法错误，补充完整逻辑：

trackingHistoryPos = myCpRegs
    // 先过滤符合条件的记录
    .Where(x => (x.Pos_Obj_TrayConcID ?? x.Pos_Obj_ContID) != null)
    // GroupJoin实现左外连接，再通过SelectMany展开分组结果
    .GroupJoin(
        trackingsHistory,
        c => c.CpRegID,
        t => t.CpRegID,
        (c, joinedTrackings) => new { CpReg = c, Trackings = joinedTrackings }
    )
    .SelectMany(
        group => group.Trackings.DefaultIfEmpty(),
        (group, ct) => new { group.CpReg, Tracking = ct }
    )
    // 添加排序
    .OrderBy(x => x.CpReg.Pos_CpRegPosID)
    // 映射到目标实体类
    .Select(x => new TrackingHistoryPosRow()
    {
        TrackingHistoryID = x.Tracking?.TrackingHistoryID,
        Obj_ContID = x.CpReg.Pos_Obj_ContID,
        Obj_TrayConcID = x.CpReg.Pos_Obj_TrayConcID,
        Obj_ProdID = x.CpReg.Pos_Obj_ProdID,
        Obj_CostCentreID = x.CpReg.Obj_CostCentreID,
        HasSN = x.CpReg.Pos_Obj_TrayConcID != null && (x.CpReg.Pos_Obj_TrayHasSN ?? false),
        Qty = x.CpReg.Pos_Qty,
        Parent_ContID = x.CpReg.Parent_ContID,
        StockLocationIDFrom = x.CpReg.StockLocationIDFrom,
        StockLocationID = x.CpReg.StockLocationID,
        IsDeleted = x.CpReg.IsRejected == 1,
        TrackTime = x.CpReg.TrackTime,
        IsPreliminary = x.CpReg.IsPreliminary == 1,
        IsPhysical = x.CpReg.IsPhysical == 1,
    })
    .ToList();

关键修正说明 #

• 左外连接后TrackingHistory的实例可能为null，必须用?.空条件访问运算符避免空引用异常
• 补全所有原始SQL中对应A表（即myCpRegs）的字段，填充原代码中的TODO项
• 添加原始SQL中缺失的过滤条件：(x.Pos_Obj_TrayConcID ?? x.Pos_Obj_ContID) != null
• 修正第一种尝试中GroupJoin的语法错误，补充分组后的展开逻辑

内容的提问来源于stack exchange，提问作者Anas Sadek