LINQ Query with Hashsets from Entity Data Model

Question

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.

Jacek Gorgoń · Accepted Answer · 2013-12-18 14:15:52Z

2

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)

answered Dec 18, 2013 at 14:15

Jacek Gorgoń

3,2141 gold badge28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

LINQ Query with Hashsets from Entity Data Model

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related