Raw SQL Commands in EF Core

What are the benefits of using raw SQL commands?

We can use EF Core methods to retrieve/update data from database. By using them we can

1. Call Store Procedure

2. Write SQL Queries in LINQ

3. Combine SQL Query and LINQ

So if you want to call SPs directly via LINQ or you don’t have a way to retrieve/modify data other than SQL Query then these methods are perfect remedy.

Methods

We have following methods

1. FromSqlInterpolated and FromSql

These methods are safe against SQL Injection attack and can be used to call SP and execute queries.

FromSql is used for parameterized queries that return entity types

2. FromRawSql

It is helpful in calling the stored procedures and and writing direct SQL in LINQ.

Be careful when using it , if not used properly it can be prey of SQL Injection attack

It is used for parameterized queries that return entity types or primitive types

var simpleSelect = _context.MdCategories
    .FromSqlRaw("SELECT * FROM MD_Category")
    .ToList();

var selectWithParam = context.MdCategories
    .FromSqlRaw("SELECT * FROM MD_Category WHERE Id = {0}", 1)
    .ToList();

var callProcedure = context.MdCategories
    .FromSqlRaw("EXECUTE dbo.GetCategories")
    .ToList();

3. ExecuteSqlRaw

If we want to perform operation like Insert/Update/Delete then we can use it.

It has async versions as well

var executeOperation = _context.Database
    .ExecuteSqlRaw(@"DELETE FROM MD_Category WHERE Id > {0}", 1);

var callProcedure = context.Database
    .ExecuteSql($"UpdateCategory @Id={id}, @Name={categoryName}");

Things to keep in mind

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

2. The column names in the result set must match the column names that properties are mapped to.