CODEWITHSUNDEEP
c#entity-frameworklinqmany-to-many
Hussein Salman
Hussein Salman

Reputation: 8256

Entity Framework Querying Many to Many using Linq

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 
}

enter image description here

Upvotes: 0

Views: 261

Answers (1)

wertzui
wertzui

Reputation: 5750

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;

Upvotes: 1

Related Questions