How to Use ExecuteSQLRaw() to Execute a Raw SQL

Summary: in this tutorial, you will learn how to use the EF Core ExecuteSQLRaw() method to execute a raw SQL statement.

Introduction to the EF Core ExecuteSQLRaw() method #

The ExecuteSQLRaw() method allows you to execute a raw SQL and return the number of rows affected. It is useful when you want to:

Execute a DELETE statement and return the number of rows deleted.
Execute an UPDATE statement and return the number of rows updated.
Execute a stored procedure and return the row affected.

Note that the ExecuteSQLRaw() method doesn’t start a transaction. To start a transaction, you need to use UseTransaction or call BeginTransaction() first.

To avoid SQL injection, you should never concatenate or interpolate ($””) the query with non-validated user-provided values to the ExecuteSQLRaw() method.

Instead, you can include parameter placeholders in the SQL query string and then provide parameters as additional arguments. For example:

var id = 1;
context.Database.ExecuteSqlRaw("DELETE FROM table WHERE Id={0}",id);

The ExecuteSQLRaw() method will automatically convert the parameters to DbParamter objects.

EF Core ExecuteSQLRaw() method examples #

We’ll use the EF Core Sample project for the demonstration. The following shows the Employees and Departments tables:

1) Using the ExecuteSQLRaw() to execute a DELETE statement #

The following example uses the ExecuteSQLRaw() method to delete an employee with id 1:

using HR;
using static System.Console;
using Microsoft.EntityFrameworkCore;

using var context = new HRContext();

var sql = @"DELETE FROM employees WHERE id={0}";
var employeeId = 1;

var rowCount = context.Database.ExecuteSqlRaw(sql, employeeId);
WriteLine($"The number of row deleted {rowCount}");

Output:

The number of row deleted 1

Behind the scenes, EF Core parameterized the query and execute it as shown in the following log:

-- [Parameters=[@p0='1'], CommandType='Text', CommandTimeout='30']
DELETE FROM employees WHERE id=@p0

The log shows that EF Core converted the id value into a DbParameter object.

2) Using the ExecuteSQLRaw() to execute an UPDATE statement #

The following example illustrates how to use the ExecuteSQLRaw() method to execute an UPDATE statement that increases the salary to 5% of the employee id 2:

using HR;
using static System.Console;
using Microsoft.EntityFrameworkCore;

using var context = new HRContext();

var sql = @"UPDATE employees SET Salary=Salary*1.05 WHERE id={0}";
var employeeId = 2;

var rowCount = context.Database.ExecuteSqlRaw(sql, employeeId);
WriteLine($"The number of row updated {rowCount}");

Output:

The number of row updated 1

EF Core sent the following UPDATE statement to the database for execution:

-- prameters=[@p0='2'], CommandType='Text', CommandTimeout='30']
UPDATE employees SET Salary=Salary*1.05 WHERE id=@p0

3) Using the ExecuteSQLRaw() to execute a stored procedure #

First, add a new migration to create a stored procedure:

add-migration CreateDeleteDepartmentSP

Second, modify the class CreateDeleteDepartmentSP in the Migrations directory to create a stored procedure that deletes a department by id in the Up() method and drops the stored procedure in the Down() method:

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace HR.Migrations;

/// <inheritdoc />
public partial class CreateDeleteDepartmentSP : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        var command = @"CREATE PROCEDURE DeleteDepartment(@Id AS INT)
                            AS
                            BEGIN
                                DELETE FROM Departments
                                WHERE Id=@Id;
                            END";
        migrationBuilder.Sql(command);
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        var command = @"DROP PROCEDURE DeleteDepartment;";
        migrationBuilder.Sql(command);
    }
}

Third, create the stored procedure by running the Update-Database command in the Package Manager Console:

Update-Database

Finally, execute the stored procedure to delete the department with id 1:

using HR;
using static System.Console;
using Microsoft.EntityFrameworkCore;

using var context = new HRContext();

var sql = @"EXECUTE DeleteDepartment {0}";
var departmentId = 1;

var rowCount = context.Database.ExecuteSqlRaw(sql, departmentId);
WriteLine($"The number of row deleted {rowCount}");

Output:

The number of row deleted 1

EF Core sent the SQL statement to the database to execute the stored procedure:

-- [Parameters=[@p0='1'], CommandType='Text', CommandTimeout='30']
EXECUTE DeleteDepartment @p0

Summary #

Use the ExecuteSQLRaw() method to execute a raw SQL and return the number of rows affected.

Was this helpful?