在遍歷EFCore5中的多個條件包含后查詢任何條目

考慮以下資料模型：

• 一個 Principal 有多個角色（多對多）；
• 角色授予多個權限（多對多）；

現在我想使用 LINQ 來確定一個原則是否有權限，即他是否在任何擁有此權限的角色中。通常我會AnyAsync在這樣的情況下使用連接表，但由于我遍歷了多個連接表，我真的很掙扎。我最初想出了這個：

    var query = context.Principals
        .Where(p => p.Id == "SomeUUID")
        .Include(p => p.Roles)
        .ThenInclude(r => r.Permissions
        .Where(p => p.Name == "SomePermission"));

但是現在我必須再次遍歷（可能在記憶體中）附加的角色通過主體來搜索Any權限。

有沒有辦法可以在同一個查詢中順利應用此檢查？

編輯：這是生成的 SQL 以補充 Parks'（底部）的答案：

SELECT CASE
      WHEN EXISTS (
          SELECT 1
          FROM [Principals] AS [a]
          INNER JOIN (
              SELECT [a1].[Id], [a1].[Description], [a1].[DisplayName], [a0].[RoleId], [a0].[PrincipalId]
              FROM [PrincipalRoles] AS [a0]
              INNER JOIN [Roles] AS [a1] ON [a0].[RoleId] = [a1].[Id]
          ) AS [t] ON [a].[PrincipalId] = [t].[PrincipalId]
          INNER JOIN (
              SELECT [a3].[Id], [a3].[Name], [a2].[RoleId], [a2].[PermissionId]
              FROM [RolePermissions] AS [a2]
              INNER JOIN [Permissions] AS [a3] ON [a2].[PermissionId] = [a3].[Id]
          ) AS [t0] ON [t].[Id] = [t0].[RoleId]
          WHERE ([a].[PrincipalId] = "SomePrincipal") AND ([t0].[Name] = "SomePermission")) THEN CAST(1 AS bit)
      ELSE CAST(0 AS bit)
END

SELECT CASE
      WHEN EXISTS (
          SELECT 1
          FROM [Principals] AS [a]
          WHERE ([a].[PrincipalId] = "SomePrincipal") AND EXISTS (
              SELECT 1
              FROM [PrincipalRoles] AS [a0]
              INNER JOIN [Roles] AS [a1] ON [a0].[RoleId] = [a1].[Id]
              WHERE ([a].[PrincipalId] = [a0].[PrincipalId]) AND EXISTS (
                  SELECT 1
                  FROM [RolePermissions] AS [a2]
                  INNER JOIN [Permissions] AS [a3] ON [a2].[PermissionId] = [a3].[Id]
                  WHERE ([a1].[Id] = [a2].[RoleId]) AND ([a3].[Name] = "SomePermission")))) THEN CAST(1 AS bit)
      ELSE CAST(0 AS bit)
END

uj5u.com熱心網友回復：

要查看主體是否在角色中具有角色，在權限中具有權限，您可以使用以下 Where 條件：

var result = await context.Principals
    .Where(p => p.Id == "SomeUUID" && p.Roles.Any(r => r.Permissions.Any(x => x.Name == "SomePermission")))
    .FirstOrDefaultAsync();

如果result為 null，則 Principal 不存在或沒有您檢查的權限。如果result仍然需要導航屬性，那么您可以添加最初包含在您的問題中的包含陳述句。

uj5u.com熱心網友回復：

可能你正在尋找這個查詢：

var hasPermssion = context.Principals
    .Where(p => p.Id == "SomeUUID")
    .SelectMany(p => p.Roles)
    .SelectMany(r => r.Permissions)
    .Any(p => p.Name == "SomePermission");

下面是生成的 SQL 陳述句：

SELECT CASE
      WHEN EXISTS (
          SELECT 1
          FROM [Principals] AS [a]
          INNER JOIN (
              SELECT [a1].[Id], [a1].[Description], [a1].[DisplayName], [a0].[RoleId], [a0].[PrincipalId]
              FROM [PrincipalRoles] AS [a0]
              INNER JOIN [Roles] AS [a1] ON [a0].[RoleId] = [a1].[Id]
          ) AS [t] ON [a].[PrincipalId] = [t].[PrincipalId]
          INNER JOIN (
              SELECT [a3].[Id], [a3].[Name], [a2].[RoleId], [a2].[PermissionId]
              FROM [RolePermissions] AS [a2]
              INNER JOIN [Permissions] AS [a3] ON [a2].[PermissionId] = [a3].[Id]
          ) AS [t0] ON [t].[Id] = [t0].[RoleId]
          WHERE ([a].[PrincipalId] = "SomePrincipal") AND ([t0].[Name] = "SomePermission")) THEN CAST(1 AS bit)
      ELSE CAST(0 AS bit)
END

標籤：实体框架 林克 .net核心 实体框架核心

上一篇：物體框架-具有多個條件的異步選擇

下一篇：Entityframework核心where條件、執行的sql字串及其結果