EF Core - multiple counts in one sql request

asp.net-core c# entity-framework-core

Accepted Answer

Due to the following, the query is being run for each user row:_db.Users.Select statement.

Use the following query to exclusively get 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 a single query, use the following syntax (note how the flag is used to indicate counts that are pending confirmation, how constants are used to group, and how sum is used to calculate counts 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();
2
6/30/2018 12:30:39 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow