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?