CODEWITHSUNDEEP
linq-to-sqlc#-4.0membership-providerroleprovider
TheDude
TheDude

Reputation: 197

Getting all users and their roles (membership and roles) with Linq2Sql, right query?

I am trying to get all users and their roles from the generated membership and roles tables, and I came up with this:

var query = from a in aspnet_Users
            select new { a.UserName, a.UserId } into b
            join c in aspnet_UsersInRoles on b.UserId equals c.UserId
            join d in aspnet_Roles on c.RoleId equals d.RoleId
            group d.RoleName by b;

Is this good or is there a better way to do this?

Upvotes: 1

Views: 2956

Answers (2)

Pleun
Pleun

Reputation: 8920

Well, using the direct approach by querying the database will work. However, you are completely bypassing Membership and all the functionality it offers.

Why not just use Membership:

var roles = from MembershipUser u in Membership.GetAllUsers()
             select new {user = u,
             roles = Roles.GetRolesForUser(u.UserName)};

This way, if in the future the database structure changes your code will still work as you do not need to know the implementation.

What do you think?

ps: I have not checked what kind of SQl is generated, so if you need super performance it might still be better to go straight to the DB

Upvotes: 3

Fabian Nicollier
Fabian Nicollier

Reputation: 2861

Yes there is. You have basically translated the SQL query to LINQ and forgotten that in LINQ you can navigate properties (i.e. user.Role) which results in automatic joins.

It would be simpler to just fetch the users while telling Linq to SQL to bring the user's roles along (or the other way: bring the roles and include the related users). Here's a link to see how to do this without lazy loading: http://www.singingeels.com/Blogs/Nullable/2008/10/27/EntityFramework_Include_Equivalent_in_LINQ_to_SQL.aspx.

Cheers

Upvotes: 0

Related Questions