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

You can do something like: dbContext.Items.Count()

You always can do a .Count()Function on an IQueryable

Edit: When the need of a FromSql is really there something like this should do the job:

var count = context.Items.FromSql("Select count(*) from items").First();

Answer 2

Try

 var firstElement = dbContext.Items
                             .FromSql (..)
                             .ToList
                             .First();

Answer 3

FromSQL has some limitations:

• The SQL query must return data for all properties of the entity or query type.

So, try this:

var elements = dbContext.Items
                         .FromSql("SELECT * from dbo.Items")
                         .ToList();

var countOfElements = elements.Count();

Answer 4

You should pass composable SELECT SQL to FromSql method, e.g. SELECT * - see Raw SQL Queries. Then you can apply regular LINQ Queryable operators, including Count:

var count = dbContext.Items
    .FromSql("select * FROM Items Where JSON_VALUE(Column, '$.json') = 'abc'")
    .Count();

Answer 5

the fastest hack/workaround is if your Item class has an int/long property (lets say Id) you can treat it like this:

   dbContext.Items.FromSql("SELECT COUNT(*) as Id
                     FROM Items 
                     WHERE JSON_VALUE(Column, '$.json') = 'abc'").Select(x=>x.Id).First();

Answer 6

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 ;.