我如何code此SQL查询在LINQ到SQL？ [英] How do I code this SQL query in Linq-To-SQL?

问题描述

我是新来的MVC及其进展顺利，但挣扎了一下与后到前SQL等。

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')

表和字段是：

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;

提前感谢

推荐答案

tvanfossen为及时答复谢谢，我试图实现自己的方式，但不能得到它的权利。我觉得你的做法是整洁补题得到了以下工作：

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;

干杯

Ĵ

这篇关于我如何code此SQL查询在LINQ到SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！