我试图从表中获取多个计数统计信息,但没有得到我想要的。
Code
var result = _db.Users.Select(g => new
{
count = _db.Users.Count(),
acCount = _db.Users.Count(u => u.User.State == AccountState.AwaitingConfirmation)
});
sql请求
SELECT (
SELECT COUNT(*)
FROM `users` AS `c`
) AS `count`, (
SELECT COUNT(*)
FROM `users` AS `u`
INNER JOIN `users` AS `u.User` ON `u`.`UserId` = `u.User`.`Id`
WHERE `u.User`.`State` = 4
) AS `acCount`
FROM `users` AS `g`
预期结果
result = { count = ?, acCount = ? }
实际结果(表中4个用户)
result = [
{ count = ?, acCount = ? },
{ count = ?, acCount = ? },
{ count = ?, acCount = ? },
{ count = ?, acCount = ? }
]
显然这是错误的,因为我正在使用Select
这给了我 N 次计数(N 是用户表中的行数)。
我想要的是能够直接一次获得这些计数,最好是在一个请求中。
我也尝试过GroupBy(i => 1)
但这会产生很多 sql 请求..
谢谢。