Querying for any entries after traversing multiple conditional includes in EF Core 5-CodePudding

Consider the following data model:

A Principal has a number of Roles (many-to-many);
Roles grant multiple Permissions (many-to-many);

Now i want to use LINQ to determine whether a Principle has a permission, i.e. whether he is in any Roles that possess this permission. Usually I would go for AnyAsync on the join table for cases like this , but since I am traversing more than one join table I am really struggling. I initially came up with this:

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

But now I would have to traverse, probably in-memory, the attached Roles through the Principal again to search for Any Permission.

Is there a way I can smoothly apply this check within the same query?

CodePudding user response：

To see if a principal has a Role in Roles that has a Permission in Permissions you can use the following Where condition:

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

If result is null then either the Principal did not exist or did not have the permission you checked for. If result still needs to have the navigation properties then you can add the include statements originally included in your question.

CodePudding user response：

Probably you are looking for this query:

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