c# - EF 上下文中基于动态角色的身份验证过滤器

Question

我一直在努力弄清楚 github 上到底发生了什么的细节。

所有这一切的关键点是我正在尝试将这样的过滤器应用于上下文......

builder.Filter("UserProcesses",
    (Process p, string user) => !p.Roles.Any() || p.Roles.Any(r => r.Users.Any(u => u.UserName == user && (bool)r.Read)),
    (CoreDataContext ctx) => ctx.AuthInfo.Name
);

这会在评估表达式树并尝试生成 SQL 查询时在 EF 中生成运行时表达式，因此我想我可以尝试其他方法并解决它并想出这个...

builder.Filter("UserProcesses",
    (Process p, string user) => !p.Roles.Any() || p.Roles.Any(r => r.Read ?? false && r.Users.Any(u => u.UserName == user)),
    (CoreDataContext ctx) => ctx.AuthInfo?.Name
);

这里的最终结果是……

var proesses = ctx.Processes.ToList();

... 是我得到了由 EF 生成的 SQL 查询(如探查器中所见)...

exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[FirstProcessStepId] AS [FirstProcessStepId], 
    [Extent1].[Name] AS [Name]
    FROM [Workflow].[Processes] AS [Extent1]
    WHERE ( NOT EXISTS (SELECT 
        1 AS [C1]
        FROM [dbo].[ProcessRoles] AS [Extent2]
        WHERE [Extent1].[Id] = [Extent2].[Process_Id]
    )) OR ( EXISTS (SELECT 
        1 AS [C1]
        FROM  [dbo].[ProcessRoles] AS [Extent3]
        INNER JOIN [dbo].[Roles] AS [Extent4] ON [Extent4].[Id] = [Extent3].[Role_Id]
        WHERE ([Extent1].[Id] = [Extent3].[Process_Id]) AND ((CASE WHEN ([Extent4].[Read] IS NULL) THEN cast(0 as bit) ELSE [Extent4].[Read] END) = 1)
    )) ',N'@DynamicFilterParam_000002 bit',@DynamicFilterParam_000002=NULL

最终结果是我的测试数据应该过滤掉 1 个流程实体，因为它链接到用户不在的角色。

Answer 1

所以我做了更多的挖掘，结果证明我们可以像这样构建过滤器......

builder.Filter("UserProcesses",
    (Process p, string user) => !p.Roles.Any() || p.Roles.Any(r => r.Read == true && r.Users.Any(u => u.UserName == user)),
    (CoreDataContext ctx) => ctx.AuthInfo?.Name
);

这里的最终结果是角色属性是在角色表达式范围内计算的，而不是在嵌套表达式内。

然后我被一个场景绊倒了，比如……一个日历有很多事件，我想要一个规则来为每个……说这样的话……

A user can see all calendars when they are in a role that grants them read access. A user can see all events in calendars that are on calendars they have access to.

这导致生成的复杂 SQL 出现问题，所以我最终写出了这样的规则......

builder.Filter("UserCalendars",
    (Calendar c, string user) => !c.Roles.Any() || c.Roles.Any(r => r.Read == true && r.Users.Any(u => u.UserName == user)),
    (CoreDataContext ctx) => ctx.AuthInfo?.Name
);

builder.Filter("UserEvents",
    (Event e, IQueryable<int> calIds) => calIds.Contains(e.CalendarId),
    (CoreDataContext ctx) => ctx.Calendars.Select(c => c.Id)
);

这允许框架从单个 Db.Events.ToList() 调用中找出它实际上应该运行一个查询以首先获取所有日历的 id，然后将其作为我问的实际事件问题的参数。

这多酷啊!!