linq EF中条件为左外联接

6uxekuva 于 2022-12-06 发布在其他

关注(0)|答案(2)|浏览(169)

我想在EF（LINQ或Lamda）中执行以下查询。

SELECT S.Id, 
       S.Name, 
       S.Description, 
       S.Active, 
       R.Id AS RoleFunctionId, 
       R.SystemRoleId, 
       ISNULL(R.AllowView, 0) AS AllowView, 
       ISNULL(R.AllowAdd, 0) AS AllowAdd, 
       ISNULL(R.AllowEdit, 0) AS AllowEdit, 
       ISNULL(R.AllowDelete, 0) AS AllowDelete 
FROM SystemRoleFunction AS S 
  LEFT OUTER JOIN RoleFunction AS R 
    ON S.Id = R.SystemRoleFunctionId 
   AND R.SystemRoleId = 21 
ORDER BY S.Id

但是我很困惑，因为这个查询带有LEFT OUTER JOIN和AND表达式。这是这里比较棘手的部分。当我把where条件或AND子句放在最后时，当我在EF中这样做时，我没有得到预期的结果。
我想查看表S中的所有记录，即使表R中没有任何相关记录。
当我尝试以下方法时，得到**“转换为值类型'System.Guid'失败，因为物化值为null。结果类型的泛型参数或查询必须使用可为null的类型。"**错误。因为某些记录从表R返回null。

var b = await (from S in _db.SystemRoleFunctions
                        join z in _db.RoleFunctions on S.Id equals z.SystemRoleFunctionId into fg
                        from R in fg.Where(x => x.SystemRoleId == 21).DefaultIfEmpty()
                               select new
                               {
                                   S.Name,
                                   S.Description,
                                   S.Active,
                                   S.Tstamp,
                                   R.Id,
                                   R.SystemRoleId,
                                   R.SystemRoleFunctionId,
                                   AllowView = R == null ? false : R.AllowView,
                                   AllowAdd = R == null ? false : R.AllowAdd,
                                   AllowEdit = R == null ? false : R.AllowEdit,
                                   AllowDelete = R == null ? false : R.AllowDelete,
                               }).ToListAsync();

linq

来源：https://stackoverflow.com/questions/73217239/left-outer-join-with-condition-in-ef

2条答案

按热度按时间

7gyucuyw1#

这是简化版本和文档：集合选择器在where子句中引用外部

var a = await (
    from S in _db.SystemRoleFunctions
    from R in _db.RoleFunctions
        .Where(x => x.SystemRoleId == 21 && S.Id == x.SystemRoleFunctionId)
        .DefaultIfEmpty()
    select new
    {
        S.Name,
        S.Description,
        S.Active,
        S.Tstamp,
        SystemRoleId = R == null ? 21: R.SystemRoleId,
        RoleFunctionId = R == null ? Guid.NewGuid() : R.Id,
        SystemRoleFunctionId = R == null ? S.Id : R.SystemRoleFunctionId,
        AllowView = R == null ? false : R.AllowView,
        AllowAdd = R == null ? false : R.AllowAdd,
        AllowEdit = R == null ? false : R.AllowEdit,
        AllowDelete = R == null ? false : R.AllowDelete,
    }
    ).ToListAsync();

赞(0）回复(0）举报 2022-12-06

ddarikpa2#

我设法用下面的方法修复了这个问题。感谢@Guidog给我指出。

var a = await (from S in _db.SystemRoleFunctions
                           join z in _db.RoleFunctions.Where(x => x.SystemRoleId == 21) on S.Id equals z.SystemRoleFunctionId into jointable
                           from R in jointable.DefaultIfEmpty()
                           select new
                           {
                               S.Name,
                               S.Description,
                               S.Active,
                               S.Tstamp,
                               SystemRoleId = R == null ? 21: R.SystemRoleId,
                               RoleFunctionId = R == null ? Guid.NewGuid() : R.Id,
                               SystemRoleFunctionId = R == null ? S.Id : R.SystemRoleFunctionId,
                               AllowView = R == null ? false : R.AllowView,
                               AllowAdd = R == null ? false : R.AllowAdd,
                               AllowEdit = R == null ? false : R.AllowEdit,
                               AllowDelete = R == null ? false : R.AllowDelete,
                           }).ToListAsync();

赞(0）回复(0）举报 2022-12-06

我来回答

linq EF中条件为左外联接

2条答案

相关问题

热门标签

最新问答