How to get Count from FromSql?

Question

I have

dbContext.Items.FromSql("SELECT COUNT(*) 
                         FROM Items 
                         WHERE JSON_VALUE(Column, '$.json') = 'abc'")

This returns an IQueryable, I am wondering how I can return a scalar int back?

Answer 1

As of EF Core 3.1, 2020

var count = dbContext.Set.FromSqlRaw(/* {raw SQL} */).Count();  

Will generate the following SQL

SELECT COUNT(*)::INT
FROM (
    -- {raw SQL}
) AS c

where {raw SQL} is of the form

select count(*) from my_table where my_col_condition = true group by my_col_id

The count work can then perfectly be done on the database-side this way, without loading table rows on the client.
Be careful not to end {raw SQL} with ;.