Return count using raw query, using Entity Framework and MVC

Question

I am using Entity Framework in an MVC website

I am trying to get just the number of records using a raw query.

I am looking for something along these lines but any will be happy with any solution at all.

var sql = SELECT COUNT(*) FROM dbo.Articles WHERE (CategoryID = 3)

var total = _context.Database.SOMETHING(sql)

I realise that for such a simple scenario, a raw query is perhaps not the way to go but in reality, the sql string is MUCH more complicated so it is next to impossible for to use Linq to SQL.

What is the type of _context? DbContext, DataContext, ObjectContext ? — nemesv
– nemesv, Commented Apr 14, 2012 at 14:57
@nemesv. It is a DbContext ( sorry, it did not realise that Entity Framework could have different things - I am obviously new to this game). Does knowing it is DbContext mean you can help me? — Pete Davies
– Pete Davies, Commented Apr 14, 2012 at 15:03

nemesv · Accepted Answer · 2012-04-14 15:05:06Z

70

You can execute raw SQL queries with EF code first with using the SqlQuery method:

var sql = "SELECT COUNT(*) FROM dbo.Articles WHERE (CategoryID = 3)";
var total = _context.Database.SqlQuery<int>(sql).First();

answered Apr 14, 2012 at 15:05

nemesv

140k16 gold badges424 silver badges364 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pete Davies Over a year ago

Brilliant! That worked perfectly after I changed the last line to var total = _context.Database.SqlQuery<int>(sql).single();

Arun D Over a year ago

But i have to use dynamic as below, var sql = "SELECT * FROM dbo.Articles"; dynamic total = _context.Database.SqlQuery<dynamic>(sql).ToList(); ..... Is this possible??

Marius Ologesa · Accepted Answer · 2023-06-05 14:35:40Z

8

An update for the approved answer above with a EF Core 7.

name the column 'AS Value'. It must be named like this, it fails otherwise.
use SqlQueryRaw because SqlQuery requires a FormattableString and 'sql' is not such. However, it can be re-written with SqlQuery.

var sql = "SELECT COUNT(*) AS Value FROM dbo.Articles WHERE (CategoryID = 3)";
var total = _context.Database.SqlQueryRaw<int>(sql).First();

with SqlQuery

int categoryId = 3;
var total = _context.Database.SqlQueryRaw<int>($"SELECT COUNT(*) AS Value FROM dbo.Articles WHERE (CategoryID = {categoryId})").First();

answered Jun 5, 2023 at 14:35

Marius Ologesa

4014 silver badges4 bronze badges

1 Comment

Giancarlo Sierra Over a year ago

Adding 'AS Value' works well in EF Core 7, thanks! You can use SqlQuery (not only SqlQueryRaw) if using interpolated strings for parameterization.

Collectives™ on Stack Overflow

Return count using raw query, using Entity Framework and MVC

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related