英文:
LINQ to find users without a given role
问题
以下是要翻译的内容:
"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"
英文:
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.
答案1
得分: 1
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.
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论