How do I code this SQL query in Linq-To-SQL?

Question

I'm new to MVC and its going well but struggling a bit with the back to front SQL etc

select * from aspnet_Users 
where Active = 1 and UserId in (select UserId from aspnet_UsersInRoles,aspnet_Roles where aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId and aspnet_Roles.RoleName = 'Auth Level 1')

Tables and fields are:

aspnet_Users (UserId int, Active bit, eMail varchar)
aspnet_UsersInRoles (UserId, RoleId)
aspnet_Roles (RoleId int, RoleName varchar)

My attempt - but incomplete looks like this so far:

        LmsDataContext db = new LmsDataContext();
        var AuthUsers = from aspnet_User in db.aspnet_Users
                        join userinroles in db.aspnet_UsersInRoles on aspnet_User.UserId equals userinroles.UserId
                        where aspnet_User.Active 
                        select aspnet_User;

Many thanks in advance

Answer 1

Assuming that you have the associations defined in the database (or the designer), you should be able to access via properties the related entities without resorting to explicit joins. The following example is untested, but should put you on the right track. The basic idea is to find if there are any associations where the associated role has the correct name and choose the user if any are found.

var users = db.aspnet_Users
              .Where( u => u.Active )
              .Any( u => u.aspnet_UsersInRoles
                          .Any( r => r.aspnet_Roles.RoleName == "Auth Level 1" ) );

Answer 2

Thanks tvanfossen for the prompt reply, I tried to implement your way but could not get it right. I think your approach is neater bu tI got the following to work :

    // Loop through all the Users on the System who are in Auth Level 1 Role and Email them
    LmsDataContext db = new LmsDataContext();
    var AuthUsers = from aspnet_User in db.aspnet_Users
                    join userinroles in db.aspnet_UsersInRoles on aspnet_User.UserId equals userinroles.UserId
                    where aspnet_User.Active 
                    && userinroles.aspnet_Role.RoleName == "Auth Level 1"
                    select aspnet_User;

Cheers

J