Csharp 使用linq进行左连接到空数据

我在MySQL中有以下查询.

SELECT Pages.*, IFNULL(PagePermission.CanRead, 0) CanRead, IFNULL(PagePermission.CanWrite, 0) CanWrite, IFNULL(PagePermission.CanDelete, 0) CanDelete
FROM (SELECT * FORM Pages WHERE IsVisible = 1) Pages
LEFT JOIN (
  SELECT PermissionPages.PageId, MAX(PermissionPages.CanRead) CanRead, MAX(PermissionPages.CanWrite) CanWrite, MAX(PermissionPages.CanDelete) CanDelete 
  FROM (SELECT * FROM Permissions WHERE IsActive = 1) Permissions
  INNER JOIN (SELECT * FROM PermissionUsers WHERE UserId = @userId) PermissionUsers ON Permissions.PermissionId = PermissionUsers.PermissionId
  INNER JOIN PermissionPages ON Permissions.PermissionId = PermissionPages.PermissionId
  GROUP BY PermissionPages.PageId
) PagePermission ON Pages.PageId = PagePermission.PageId

这里，值@userId来自应用程序；对于新创建的用户(即，其页面权限尚未定义的用户)，结果PagePermission将为空.

现在，我try 用LINQ编写这个查询，并考虑到前面提到的条件来执行新的查询.

为此，我最初编写了以下查询.

var result = (from pages in Pages.Where(x => x.IsVisible)
              join pagePermission in (from permissions in Permissions.Where(x => x.IsActive)
                                      join permissionUsers in PermissionUsers.Where(x => x.UserId == userId)
                                      on permissions.PermissionId equals permissionUsers.PermissionId
                                      join permissionPages in PermissionPages
                                      on permissions.PermissionId equals permissionPages.PermissionId
                                      group permissionPages by permissionPages.PageId into groupedPermissionPages
                                      select new
                                      {
                                          PageId = groupedPermissionPages.Key,
                                          CanRead = groupedPermissionPages.Select(p => p.CanRead).Max(),
                                          CanWrite = groupedPermissionPages.Select(p => p.CanWrite).Max(),
                                          CanDelete = groupedPermissionPages.Select(p => p.CanDelete).Max()
                                      })
              on pages.PageId equals pagePermission.PageId into pagePermissionJoined
              from pagePermission in pagePermissionJoined.DefaultIfEmpty()
              select new PagePermissionResult
              {
                  PageId = pages.PageId,
                  PageName = pages.PageName,
                  .....
                  .....
                  CanRead = (pagePermission == null ? false : pagePermission.CanRead),
                  CanWrite = (pagePermission == null ? false : pagePermission.CanWrite),
                  CanDelete = (pagePermission == null ? false : pagePermission.CanDelete)
              }).ToList();

当我try 为一个新用户执行这个查询时，我得到了以下异常.

The LINQ expression '(GroupByShaperExpression:
KeySelector: (t.PageId), 
ElementSelector:(EntityShaperExpression: 
    EntityType: PermissionPages
    ValueBufferExpression: 
        (ProjectionBindingExpression: EmptyProjectionMember)
    IsNullable: False
)
)
    .Select(p => p.CanRead)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

在此之后，我将查询修改为:

var result = (from pages in Pages.Where(x => x.IsVisible)
              join pagePermission in (from permissions in Permissions.Where(x => x.IsActive)
                                      join permissionUsers in PermissionUsers.Where(x => x.UserId == userId)
                                      on permissions.PermissionId equals permissionUsers.PermissionId
                                      join permissionPages in PermissionPages
                                      on permissions.PermissionId equals permissionPages.PermissionId
                                      select permissionPages).ToList()
                                                             .GroupBy(x => new { PageId = x.PageId })
                                                             .Select(x => new 
                                                             {
                                                                 PageId = x.Key.PageId,
                                                                 CanRead = x.Select(p => p.CanRead).Max(),
                                                                 CanWrite = x.Select(p => p.CanWrite).Max(),
                                                                 CanDelete = x.Select(p => p.CanDelete).Max()
                                                             })
              on pages.PageId equals pagePermission.PageId into pagePermissionJoined
              from pagePermission in pagePermissionJoined.DefaultIfEmpty()
              select new PagePermissionResult
              {
                  PageId = pages.PageId,
                  PageName = pages.PageName,
                  .....
                  .....
                  CanRead = (pagePermission == null ? false : pagePermission.CanRead),
                  CanWrite = (pagePermission == null ? false : pagePermission.CanWrite),
                  CanDelete = (pagePermission == null ? false : pagePermission.CanDelete)
              }).ToList();

对新用户执行此查询导致以下异常:

Processing of the LINQ expression 'DbSet<Pages>
    .Where(x => x.IsVisible)
    .LeftJoin(
        outer: __p_1
            .AsQueryable(), 
        inner: pages => pages.PageId, 
        outerKeySelector: pagePermission => pagePermission.PageId, 
        innerKeySelector: (pages, pagePermission) => new PagePermissionResult{ 
            PageId = pages.PageId, 
            PageName = pages.PageName, 
            ..... 
            ..... 
            CanRead = pagePermission == null ? False : pagePermission.CanRead, 
            CanWrite = pagePermission == null ? False : pagePermission.CanWrite, 
            CanDelete = pagePermission == null ? False : pagePermission.CanDelete 
        }
    )' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

然后，我将查询修改为:

var result = (from pages in Pages.Where(x => x.IsVisible)
              join pagePermission in (from permissions in Permissions.Where(x => x.IsActive)
                                      join permissionUsers in PermissionUsers.Where(x => x.UserId == userId)
                                      on permissions.PermissionId equals permissionUsers.PermissionId
                                      join permissionPages in PermissionPages
                                      on permissions.PermissionId equals permissionPages.PermissionId
                                      select permissionPages).ToList()
                                                             .GroupBy(x => new { PageId = x.PageId })
                                                             .Select(x => new 
                                                             {
                                                                 PageId = x.Key.PageId,
                                                                 CanRead = x.Select(p => p.CanRead).Max(),
                                                                 CanWrite = x.Select(p => p.CanWrite).Max(),
                                                                 CanDelete = x.Select(p => p.CanDelete).Max()
                                                             })
              on pages.PageId equals pagePermission.PageId into pagePermissionJoined
              from pagePermission in pagePermissionJoined.DefaultIfEmpty(new { PageId = pages.PageId, CanRead = false, CanWrite = false, CanDelete = false })
              select new PagePermissionResult
              {
                  PageId = pages.PageId,
                  PageName = pages.PageName,
                  .....
                  .....
                  CanRead = (pagePermission == null ? false : pagePermission.CanRead),
                  CanWrite = (pagePermission == null ? false : pagePermission.CanWrite),
                  CanDelete = (pagePermission == null ? false : pagePermission.CanDelete)
              }).ToList();

此查询为新用户生成以下例外:

Expression of type 'System.Collections.Generic.IEnumerable`1[<>f__AnonymousType23`5[System.Int32,System.Boolean,System.Boolean,System.Boolean,System.Boolean]]' cannot be used for parameter of type 'System.Linq.IQueryable`1[<>f__AnonymousType23`5[System.Int32,System.Boolean,System.Boolean,System.Boolean,System.Boolean]]' of method 'System.Linq.IQueryable`1[<>f__AnonymousType23`5[System.Int32,System.Boolean,System.Boolean,System.Boolean,System.Boolean]] Where[<>f__AnonymousType23`5](System.Linq.IQueryable`1[<>f__AnonymousType23`5[System.Int32,System.Boolean,System.Boolean,System.Boolean,System.Boolean]], System.Linq.Expressions.Expression`1[System.Func`2[<>f__AnonymousType23`5[System.Int32,System.Boolean,System.Boolean,System.Boolean,System.Boolean],System.Boolean]])' (Parameter 'arg0')

为了识别生成异常的匿名类型，我将查询修改为:

var result = (from pages in Pages.Where(x => x.IsVisible)
              join pagePermission in (from permissions in Permissions.Where(x => x.IsActive)
                                      join permissionUsers in PermissionUsers.Where(x => x.UserId == userId)
                                      on permissions.PermissionId equals permissionUsers.PermissionId
                                      join permissionPages in PermissionPages
                                      on permissions.PermissionId equals permissionPages.PermissionId
                                      select permissionPages).ToList()
                                                             .GroupBy(x => new { PageId = x.PageId })
                                                             .Select(x => new PermissionPages
                                                             {
                                                                 PageId = x.Key.PageId,
                                                                 CanRead = x.Select(p => p.CanRead).Max(),
                                                                 CanWrite = x.Select(p => p.CanWrite).Max(),
                                                                 CanDelete = x.Select(p => p.CanDelete).Max()
                                                             })
              on pages.PageId equals pagePermission.PageId into pagePermissionJoined
              from pagePermission in pagePermissionJoined.DefaultIfEmpty(new PermissionPages { PageId = pages.PageId, CanRead = false, CanWrite = false, CanDelete = false })
              select new PagePermissionResult
              {
                  PageId = pages.PageId,
                  PageName = pages.PageName,
                  .....
                  .....
                  CanRead = (pagePermission == null ? false : pagePermission.CanRead),
                  CanWrite = (pagePermission == null ? false : pagePermission.CanWrite),
                  CanDelete = (pagePermission == null ? false : pagePermission.CanDelete)
              }).ToList();

此修改将生成的异常更改为:

Expression of type 'System.Collections.Generic.IEnumerable`1[PermissionPages]' cannot be used for parameter of type 'System.Linq.IQueryable`1[PermissionPages]' of method 'System.Linq.IQueryable`1[PermissionPages] Where[PermissionPages](System.Linq.IQueryable`1[PermissionPages], System.Linq.Expressions.Expression`1[System.Func`2[PermissionPages,System.Boolean]])' (Parameter 'arg0')

在这一点上，我不确定我还能做些什么来为未定义权限的用户运行查询.

我没有包括使用具有定义权限的用户来生成结果的场景--因为这类用户的数据可以简单地使用INTER JOIN来获取.

此外，虽然我可以在单独的查询中获得pagePermission的数据，然后使用该结果生成最终的PagePermissionResult数据；但我这样做并不是为了避免重复访问数据库.

提前感谢您花时间阅读这个问题，并向我提出了解决方案.