9

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
chobo2
  • 83,322
  • 195
  • 530
  • 832

6 Answers6

5

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

Alexandre Daubricourt
  • 3,323
  • 1
  • 34
  • 33
  • this will always return 1 since your raw sql already performs a count and you add another Count() on top of it, no? – Romain Hautefeuille Dec 18 '20 at 13:23
  • @RomainHautefeuille No cause the nested `count` ends with `group by my_col_id` – Alexandre Daubricourt Dec 18 '20 at 15:47
  • 1
    I'm upvoting because this answer helped me, however I don't see the point of adding a `GROUP BY` and a `COUNT` in the raw SQL query. Just writing a `SELECT *` and performing a `CountAsync()` in C# generates exactly the query I needed. Also, `FromSqlRaw` returns an `IQueryable`, meaning no data is returned to the client at this point. The SQL query is executed only when we reach the `CountAsync()` instruction. – Jérôme MEVEL May 17 '21 at 09:39
2

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();
tamys
  • 21
  • 2
1

Try 

 var firstElement = dbContext.Items
                             .FromSql (..)
                             .ToList
                             .First();
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
1

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();
Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343
  • Yea but won't that return all the objects and then count them, not as good as just getting a count back...but reading Marco's post this seems to be the only way. – chobo2 Nov 07 '18 at 17:17
  • No, *composable* means EF Core will create SQL on top of it, e.g. something like `SELECT Count(*) FROM (your select SQL here)` – Ivan Stoev Nov 07 '18 at 17:24
  • Btw, looking at your recent EF Core related questions, looks like you use `FromSql` just because you need `JSON_VALUE` function. Then you may find this https://stackoverflow.com/questions/52017204/expression-tree-to-sql-with-ef-core/52018037#52018037 interesting, because it allows you to use regular LINQ query with all its benefits. – Ivan Stoev Nov 07 '18 at 17:27
0

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();
Willie
  • 352
  • 1
  • 3
  • 15
  • Normally that would work, but I actually removed the where clauses to simplify my example and I need to use FromSQl as the where clause is a condition on a column that is JSON and EF Core can't do where clauses with linq on JSON – chobo2 Nov 06 '18 at 20:17
  • Something like this should work: `var count = context.Items.FromSql("Select count(*) from items").First();` – Willie Nov 06 '18 at 20:29
  • That still comes back as an "Item" object and not an int. – chobo2 Nov 06 '18 at 20:31
  • @chobo2 In that case this might help you. https://stackoverflow.com/questions/29852912/return-anonymous-type-using-sqlquery-raw-query-in-entity-framework – Willie Nov 06 '18 at 20:36
  • 1
    I will have to try that out, should be find I guess as I just need a int back. – chobo2 Nov 07 '18 at 17:17
-1

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();
Marco Hurtado
  • 534
  • 3
  • 13