linq equivalent sql query "not in (select query)"

Question

my sql code is as follows:

select UserId,UserName 
from aspnet_Users 
where UserId not in (select UsersId from tbluser  where active='true')

what is the equivalent linq expression?

Answer 1

my first try using LiNQ in C#

var result = from y in aspnet_Users
            where !(
                        from x in tblUser
                        where  x.active == "true"
                        select x.UsersID
                    ).Contains(y.UserId)
            select y;                
            -- OR // select new { y.UserId, y.UserName};

SOURCE

• The NOT IN clause in LINQ to SQL