How to Count on a specific column using CountAsync EF Core

Question

On MSDN https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/sort-filter-page?view=aspnetcore-6.0#add-paging-to-students-index they use _context.DBSet.CountAsync() to count the number of rows on the table. This query is translated to SELECT COUNT(*) FROM DBSET . How can I count on a specific column? For example SELECT COUNT(ID) FROM DBSET.

Answer 1

If you select count from whole recordset, you can apply filer with check to null, it is equivalent to Count(expr) SQL

var cnt = await _context.DBSet.CountAsync(x => x.ID != null); 

With grouping it is possible to reproduce Count(expr) generation

var query = _context.DBSet.GroupBy(x => x.Some)
   .Select(g => new 
    {
        g.Key,
        Count = g.Select(s => s.ID).Count()
    }); 

Or with grouping by constant

var query = _context.DBSet.GroupBy(x => 1)
   .Select(g => g.Select(s => s.ID).Count()); 

var cnt = await query.FirstAsync();