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);Code language: C# (cs)

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}");Code language: C# (cs)

Output:

The number of row deleted 1Code language: C# (cs)

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=@p0Code language: C# (cs)

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}");Code language: C# (cs)

Output:

The number of row updated 1Code language: C# (cs)

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=@p0Code language: C# (cs)

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

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

add-migration CreateDeleteDepartmentSPCode language: C# (cs)

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);
    }
}Code language: C# (cs)

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

Update-DatabaseCode language: C# (cs)

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}");
Code language: C# (cs)

Output:

The number of row deleted 1Code language: C# (cs)

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

-- [Parameters=[@p0='1'], CommandType='Text', CommandTimeout='30']
EXECUTE DeleteDepartment @p0Code language: C# (cs)

Summary

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

Was this tutorial helpful ?