I am trying to convert a SQL Query to Linq to Entities using "Not IN"

Viewed 56

I have a SQL query (stored procedure) that I am having a hard time trying to write in Linq using "NOT IN".

This is the SQL query:

ALTER PROCEDURE [dbo].[spGetAvailableRoles2User]
    @Id nvarchar(128)
AS 
BEGIN
    SET NOCOUNT ON;

    SELECT name 
    FROM Role 
    WHERE name NOT IN (SELECT t3.Name
                       FROM [User] t1 
                       JOIN [UserRole] t2 ON t1.Id = t2.UserId 
                       JOIN [Role] t3 ON t2.RoleId = t3.RoleId
                       WHERE t1.Id = @Id)
END

And here is what I am trying to do in in my MVC action Method

var result = (from x in db.Users
              join y in db.IdentityUserRoles on x.Id equals y.UserId
              join z in db.Roles on y.RoleId equals z.Id 
              where x.Id == id 
              select z.Name);

Could some explain what I am doing wrong?

1 Answers
Related