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.
Using Rule 7 and Rule 9 from my SQL to LINQ Recipe you can convert the LEFT JOIN
s 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.