Entity Framework Querying Many to Many using Linq

Question

I need to create the lambda query that allows me to get the security objects that the user (Account) is authorized to access in addition to the list of permissions assigned to him for each security object

The method is List<AuthObject> GetUserAccessList (int accountId)

The AuthObject:

 public class AuthObject
{
    public string Obj { get; set; } // name of the security object
    public List<String> Permissions { get; set; } //name of the permissions 
}

Answer 1

Query your data using 2 queries and then assemble them in memory.

var securityObjects = await context.SecurityObjects.Where(so => so.SecurityPermissions.Any(sp => sp.SecurityRoles.Any(sr => sr.Accounts.Any(a => a.ID == accountId)))).ToListAsync().COnfigureAwait(false);
var securityPermissionsByObjectId = (await context.SexurityPermissions.Where(sp =>sp.SecurityRoles.Any(sr => sr.Accounts.Any(a => a.ID == accountId))).ToListAsync().ConfigureAwait(false)).GroupBy(sp => sp.SecurityObjectID).ToDictionary(g => g.Key, g => g.Select(sp => sp.Name).ToList());

var result = securityObjects.Select(so => new AuthObject
{
    Obj = so.Name,
    Permissions = securityPermissionsByObjectId[so.ID]
})
.ToList();

return result;