CODEWITHSUNDEEP
c#asp.net-mvclinqasp.net-mvc-5asp.net-identity
Jonesopolis
Jonesopolis

Reputation: 25370

LINQ Query with Hashsets from Entity Data Model

This question may be a bit primitive but I'm having trouble grasping the correct query logic.

I have an MVC 5 Application, and I'm trying out the ADO.NET Entity Data Model to build up my entities from the database. I'm also using the new Identity Membership, and letting ASP.NET create the membership tables for me.

Here's my simple SQL Query 

SELECT p.* FROM [User] u
   INNER JOIN UserRole ur ON ur.UserId = u.UserId
   INNER JOIN [Role] r ON r.RoleID = ur.RoleId
   INNER JOIN RolePermission rp ON rp.RoleId = r.RoleID
   INNER JOIN Permission p ON p.PermissionId = rp.PermissionId
WHERE u.UserName = 'admin'

which returns me all permissions for a user, based on what roles the user is in; and works fine.

When the Entity Data Model generates the model, it removes the UserRole and RolePermission tables (tables with a composite primary key) and instead give me a HashSet in each object.

so User looks like:

public User()
{
   this.Role = new HashSet<Role>();
}

and Role:

public Role()
{
   this.User = new HashSet<User>();
   this.Permission = new HashSet<Permission>();
}

and same for Permission. I'm wondering what the best way to run that same query is in LINQ? Thankyou for any advice.

Upvotes: 0

Views: 1274

Answers (1)

Jacek Gorgoń
Jacek Gorgoń

Reputation: 3214

This should be about right (using my favourite syntax):

var permissions = Context.Users
    .Where(u => u.UserName == "admin")
    .SelectMany(u => u.Role)
    .SelectMany(r => r.Permission)

Upvotes: 2

Related Questions