Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Asp.Net Identity find users not in role

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()
like image 538
goroth Avatar asked Dec 10 '22 22:12

goroth


1 Answers

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));
like image 140
tmg Avatar answered Jan 01 '23 00:01

tmg