LINQ to find users without a given role

Question

There is an audit table with which users interacted with the system, I need to build a LINQ query that does the following SQL. The goal of this is to pull back all the rows EXCEPT those made by users with the "TargetRole":

SELECT distinct a.* 
  FROM AuditTable AS                 a
           JOIN      MyUsers         u  ON a.PaPersonId = u.PaPersonId
           LEFT JOIN AspNetUserRoles ur ON u.Id = ur.UserId
           LEFT JOIN AspNetRoles     r  ON ur.RoleId = r.Id AND r.Name = 'TargetRole'
 WHERE r.Id IS NULL

In the actual query there is more going on in the where clause, but I cannot figure out how to form the LINQ statement (query notation) to exclude the target role.

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Mar 06 '23 at 20:44
Doesn't look like it. The real stinker is the multi part ON clause on the AspNetRoles which need to be null. That is where I am really stumped. — Sam Carleton, Mar 06 '23 at 20:56
Using rule 9, translate the left joins. Then just test for `r == null`. — NetMage, Mar 06 '23 at 21:08
Sorry, you need to use rule 7 and rule 9 for the last left join. — NetMage, Mar 06 '23 at 21:14

NetMage · Accepted Answer · 2023-03-08T20:52:00.827

Using Rule 7 and Rule 9 from my SQL to LINQ Recipe you can convert the LEFT JOINs to LINQ join with DefaultIfEmpty() and using anonymous objects for multiple equality conditions:

var ans = (from a in AuditTable
           join u in MyUsers on a.PaPersonId equals u.PaPersonID
           join ur in AspNetUserRoles on u.Id equals ur.UserId into urg
           from ur in urg.DefaultIfEmpty()
           join r in AspNetRoles on new { Id = ur.RoleId, Name = "TargetRole" } equals new { r.Id,  r.Name } into rg
           from r in rg.DefaultIfEmpty()
           where r == null
           select a)
          .Distinct();

It may also be possible to replace from r in rg.DefaultIfEmpty() where r == null with where !rg.Any() - I am not sure how well it will translate.

LINQ to find users without a given role

1 Answers1