我有一个 Select 给定分支机构用户的查询.
SELECT u.first, u.last, u.id,
(SELECT u1.user_type
FROM users_branches AS u1
WHERE u.id = u1.user_id AND u1.branch_id = @foo
LIMIT 1)
FROM users AS u
WHERE EXISTS
(SELECT 1
FROM users_branches AS u0
WHERE u.id = u0.user_id AND u0.branch_id = @foo)
下面是生成该SQL的EF Core LINQ查询.
int branchId = 123;
var users = await Users
.Where(x => x.UsersBranches.Any(ub => ub.BranchId == branchId))
.Select(user => new
{
UserId = user.Id,
FirstName = user.First,
LastName = user.Last,
UserType = user.UsersBranches.FirstOrDefault(x => x.BranchId == branchId).UserType
}).ToListAsync();
我不喜欢重复WHERE
子句,只是说"只为我获取具有该分支的USERS_BRANKS记录的用户,并从该记录返回一些数据".有没有更有效的方法来做到这一点?我可以想象使用GROUP BY的解决方案,但我不确定这实际上是否会更好.