how to get all user roles while using entity framework

Question

I am using entity framework and membership in my asp.net application.

In one of the pages I need to show all the users and ther roles by joing "aspnet_Membership", "aspnet_Users", "aspnet_Roles" and "aspnet_UsersInRoles" tables.

But whenever i try to add all these tables in the .edmx the "aspnet_UsersInRoles" entity is not getting created instead the "aspnet_Users" and "aspnet_Roles" are getting associated with a Many to Many association. And i can't refrence the "aspnet_UsersInRoles" table, its throwing an compilation error.

Please help me to get the user roles, this is my link query

var users = (from membership in IAutoEntity.aspnet_Membership
                                from user in IAutoEntity.aspnet_Users
                                from role in IAutoEntity.aspnet_Roles
                                where membership.IsApproved == true & membership.UserId == user.UserId 
                                select new { user.UserName, membership.Email, user.aspnet_Roles.TargetRoleName, membership.CreateDate, user.LastActivityDate, membership.IsApproved }).ToList();

UsersInRoles is actually a many-to-many association table - so what's the issue if EF model it as such. You should able to navigate to user's roles (and vice-verse) using navigation property for the association. — VinayC
@VinayC - Not able to do that can you share with me the code — Bibhu
see my answer - I believe that there is issue in your select part. — VinayC

VinayC VinayC · Accepted Answer · 2011-09-27T11:06:23

Assuming that EF is modelling many-to-many association with navigation property called aspnet_Roles in the user class, your linq query is almost correct. Only issue is user.aspnet_Roles.TargetRoleName where you had tried to include many values (for role names) per user in your select.

Remember that you have many roles per user. So you can try out select as

select new { user.UserName, membership.Email, user.aspnet_Roles, membership.CreateDate ...

Note that you will get the roles collection for each user i.e. users[0].aspnet_roles will be the collection.

If there is guarantee that there will always one role associated with the user then you can use syntax such as

select new { user.UserName, membership.Email, user.aspnet_Roles.First().TargetRoleName, membership.CreateDate ...

Note that we are selecting the name of first role from roles associated with the user. If there is no role for the user then First() method will throw an exception. Besides, I am not certain if selecting only first role's name would suffice from application logic perspective.

how to get all user roles while using entity framework

1 Answers