EF Core - multiple counts in one sql request

Question

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

Code

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

Sql request

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`

Expected Result

result = { count = ?, acCount = ? }

Actual Result (4 users in the table)

result = [
    { count = ?, acCount = ? },
    { count = ?, acCount = ? },
    { count = ?, acCount = ? },
    { count = ?, acCount = ? }
]

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.

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

Thank you.

Answer 1

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)
};

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();