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()
In c# you can get all users that are not in a certain role like this:
var role = context.Roles.SingleOrDefault(m => m.Name == "role");
var usersNotInRole = context.Users.Where(m => m.Roles.All(r => r.RoleId != role.Id));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With