I need to find all users that DONT'T contain a certain role, using Asp.Net Identity.
So far I have something like this but it is not working.
(From role In context.Roles From userRoles In role.Users Join us In context.Users On us.Id Equals userRoles.UserId Where role.Name <> "SomeRole" Select us.UserName)
This give me a list of all users but it includes users in role "SomeRole".
It looks I need some type of not in sub query?
Here is the SQL code that works but I would still like the LINQ query if possible.
select distinct A.UserName from AspNetUsers A inner join AspNetUserRoles UR on UR.UserId = A.Id inner join AspNetRoles R on R.Id = UR.RoleId where not exists( select AspNetUserRoles UR1 on UR1.UserId = A.Id inner join AspNetRoles R1 on R1.Id = UR1.RoleId where R1.Name = 'SomeRole')
Well I have a working solution but it is not pretty and I'm sure it can be written better.
(From role In context.Roles From userRole In role.Users Join user In context.Users On us.Id Equals userRole.UserId Where Not ( From role1 In context.Roles From userRole1 In role1.Users Join user1 In context.Users On user1.Id Equals userRoles1.UserId Where role1.Name = "SomeRole" Select user1.Id).Contains(user.Id) Select user.UserName).Distinct()