LINQ用于查找没有特定角色的用户。

huangapple go评论55阅读模式
英文:

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

英文:

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.

huangapple
  • 本文由 发表于 2023年3月7日 01:56:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/75654219.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定