使用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的结果将为空。
现在，我尝试在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();

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

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')

此时，我不确定还能做什么来为未定义权限的用户运行查询。
我没有包括使用具有定义权限的用户来生成结果的场景-因为这种用户的数据可以简单地使用内部联接来获取。
此外，虽然我可以在单独的查询中获得pagePermission的数据，然后使用该结果生成最终的PagePermissionResult数据;我这样做不是为了避免重复的数据库访问。
提前感谢您花时间阅读问题并提出解决方案。

使用LINQ左联接到空数据

1条答案

相关问题

热门标签

最新问答