How to encode this SQL query in Linq-To-SQL?

Question

I am new to MVC and everything is going well, but struggling a bit with SQL and vice versa.

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:

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:

        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;

Thank you very much in advance

Answer 1

Thanks tvanfossen for the quick response, I tried to implement my path, but could not figure it out correctly. I think your approach is not suitable for 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;

Greetings

J

Answer 2

, , ( ), , . , . , , - , , , .

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