如何在LINQ中正确执行LEFT JOIN

本文关键字：执行 LEFT JOIN LINQ | 更新日期: 2023-09-27 17:58:00

这是我的查询。我想在LINQ 中这样做

SELECT tc.AppId, 
   tc.ConfigCode, 
   tc.ConfigId, 
   tc.ConfigType, 
   COALESCE(tm.ConfigValue, tc.ConfigValue) AS ConfigValue, 
    CASE 
      WHEN tc.ConfigType = 'Application' 
         THEN tc.AppId
      ELSE tm.Id
    END as Id,
FROM   dbo.Configs tc 
   LEFT JOIN dbo.ConfigValues tm 
          ON tc.ConfigId = tm.ConfigId

这是我的查询，我没有得到正确的结果

db.Configs
.Join(db.ConfigValues.DefaultIfEmpty(), tc => tc.ConfigId, tm => tm.ConfigId, (tc, tm) => new { tc = tc, tm = tm })
.Select(r => new {
    AppId = (Guid?)r.tc.AppId,
    ConfigCode = r.tc.ConfigCode,
    ConfigId = r.tc.ConfigId,
    ConfigValue = (r.tm.ConfigValue ?? r.tc.ConfigValue),
    Id = (Guid)(r.tc.ConfigType == "Application" ? r.tc.AppId : r.tm.Id),
});

如何在LINQ中正确执行LEFT JOIN

您的查询可能看起来像


db.Configs
.LeftJoin(db.ConfigValues, tc => tc.ConfigId, tm => tm.ConfigId)
.Select(r => new {tc = r.Left, tm = r.Right})
.Select(r => new {
    AppId = (Guid?)r.tc.AppId,
    ConfigCode = r.tc.ConfigCode,
    ConfigId = r.tc.ConfigId,
    ConfigValue = (r.tm.ConfigValue ?? r.tc.ConfigValue),
    Id = (Guid)(r.tc.ConfigType == "Application" ? r.tc.AppId : r.tm.Id),
});

如果您使用以下扩展方法。当使用流畅版本的LINQ时，它将使您的代码更具可读性，并且可以重复使用，因为您可以在其他LINQ查询中使用LeftJoin/RightJoin。


public class LeftJoinResult
{
    public TLeft Left { get; set; }
    public TRight Right { get; set; }
}
public class RightJoinResult
{
    public TRight Right { get; set; }
    public TLeft Left { get; set; }
}
public static IQueryable> LeftJoin(this IQueryable left, IQueryable right, Expression> leftKeySelector, Expression> rightKeySelector)
    where TRight : class
{
    return left.GroupJoin(right, leftKeySelector, rightKeySelector, (l, r) => new
    {
        Left = l,
        Matches = r.DefaultIfEmpty(),
    })
    .SelectMany(j => j.Matches, (j, m) => new LeftJoinResult
    {
        Left = j.Left,
        Right = m,
    });
}
public static IQueryable> RightJoin(this IQueryable right, IQueryable left, Expression> rightKeySelector, Expression> leftKeySelector)
    where TLeft : class
{
    return right.GroupJoin(left, rightKeySelector, leftKeySelector, (r, l) => new
    {
        Right = r,
        Matches = l.DefaultIfEmpty(),
    })
    .SelectMany(j => j.Matches, (j, m) => new RightJoinResult
    {
        Right = j.Right,
        Left = m,
    });
}

我认为您只缺少一个DefaultIfEmpty（）。"？"在r.tm？中？。ConfigValue？？。。。捕获"r.tm"的NullPointer

db.Configs
.Join(db.ConfigValues.DefaultIfEmpty(), tc => tc.ConfigId, tm => tm.ConfigId, (tc, tm) => new { tc = tc, tm = tm })
.Select(r => new {
    AppId = (Guid?)r.tc.AppId,
    ConfigCode = r.tc.ConfigCode,
    ConfigId = r.tc.ConfigId,
    ConfigValue = (r.tm.ConfigValue ?? r.tc.ConfigValue),
    Id = (Guid)(r.tc.ConfigType == "Application" ? r.tc.AppId : r.tm.Id)
}).DefaultIfEmpty(AppId = (Guid?)r.tc.AppId,
                  ConfigCode = r.tc.ConfigCode,
                  ConfigId = r.tc.ConfigId,
                  ConfigValue = (r.tm?.ConfigValue ?? r.tc.ConfigValue),
                  Id = (Guid)(r.tc.ConfigType == "Application" ? r.tc.AppId : r.tm.Id));
相关文章：