asp.net-corelinq-to-entitiesasp.net-core-identity

LINQ to find users without a given role


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.


Solution

  • 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.