EF Core-一个SQL请求中有多个计数 [英] EF Core - multiple counts in one sql request

问题描述

我正在尝试从一个表中获取多个计数统计信息，但没有得到我想要的.

I am trying to get multiple count stats from a table but I'm not getting what I want.

代码

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是users表中的行数).
我想要的是能够直接一次获得这些计数，最好是在一个请求中.

Obviously this is wrong because I am using Select which gives me the counts N times (N being the number of rows in the users table).
What I want is to be able to get these counts directly and once, preferably in one request.

我还尝试了 GroupBy(i => 1)，但这给出了很多sql请求.

I also tried GroupBy(i => 1) but that gives A LOT of sql requests..

谢谢.

推荐答案

由于 _db.Users.Select 语句，正在对用户的每一行执行查询.

The query is being executed for each row of the user due to _db.Users.Select statement.

您可以使用以下查询仅从表中获取计数:

You can use below query to get only counts from tables:

var counts = new {
    count = _db.Users.Count(),
    acCount = _db.Users.Count(u => u.User.State == AccountState.AwaitingConfirmation)
};

要在单个查询中获得两个计数-如注释中所述(注意，使用标志标识待确认的确认并按常量分组，然后使用sum来基于标志获取计数):

To get both counts in single query - as mentioned in the comments (notice using flag to identify awaiting confirmation and grouping by constants and then using sum to get count based on the flag):

  var counts = 
     _db.Users
    .Select(e => new { AwaitingConfirmation = 
             u.User.State == AccountState.AwaitingConfirmation ? 1 : 0 })
    .GroupBy(e => 1)
    .Select(g => new
    {
        Count = g.Count(),
        account = g.Sum(e => e.AwaitingConfirmation)
    }).FirstOrDefault();

这篇关于EF Core-一个SQL请求中有多个计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！