Dynamic role based auth filters in EF context

Question

I have been trying to work out the details of what exactly is going on over on github

The key points in all this are that I am trying to apply a filter like this to the context ...

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

This results in a runtime expression in EF when evaluating the expression tree and attempting to generate the SQL query, so I figured I could try something else and work around it and came up with this ...

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

The net result here when doing ...

var proesses = ctx.Processes.ToList();

... is that I get this SQL query generated by EF (as seen in profiler) ...

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

The net result is that my test data should have 1 process entity filtered out as it's linked to a role that the user is not in.

Answer 1

So I did some more digging and it turns out that we can build out the filter like this ...

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

The net result here is that the role property is evaluated in the role expression scope instead of inside the nested expression.

I then tripped over a scenario with things like say ... A calendar has many events, and I want a rule that says something like this for each ...

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.

This caused an issue in the complex SQL being generated so I ended up writing out the rules like this ...

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

This allowed the framework to figure out from a single Db.Events.ToList() call that it should in fact run a query to get the id's of all calendars first, then pass that as a parameter in to the actual event question I asked.

How cool is that!!