CODEWITHSUNDEEP
sql-serverasp.net-mvcentity-framework
Pete Davies
Pete Davies

Reputation: 1031

Return count using raw query, using Entity Framework and MVC

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.

Upvotes: 32

Views: 33707

Answers (2)

Marius Ologesa
Marius Ologesa

Reputation: 381

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

  1. name the column 'AS Value'. It must be named like this, it fails otherwise.
  2. 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();

Upvotes: 6

nemesv
nemesv

Reputation: 139758

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();

Upvotes: 70

Related Questions