FromSqlRaw vs ExecuteSqlRaw in ASP.NET Core

Suggested Videos
Part 30 - EF core migrations in razor pages project | Text | Slides
Part 31 - Repository pattern in asp.net core razor pages | Text | Slides
Part 32 - Using stored procedure in entity framework core | Text | Slides

In this video we will discuss the difference between FromSqlRaw and ExecuteSqlRaw and when to use one over the other.


FromSqlRaw 

We discussed the use of this method with an example in our previous video of this razor pages tutorial. As the name implies this method is used to execute raw sql or stored procedures.


ExecuteSqlRaw 

As the name implies, even this method is used to execute raw sql or stored procedures. So the obvious question is what's the difference and when to use one over the other.

FromSqlRaw vs ExecuteSqlRaw

Use FromSqlRaw to execute a SQL query or stored procedure that returns entities. Use ExecuteSqlRaw to execute a SQL query or stored procedure that performs database operations but does not return entities. 

For example to execute a sql query or stored procedure that returns list of employees use FromSqlRaw 

public IEnumerable<Employee> GetAllEmployees()
{
    return context.Employees
                    .FromSqlRaw<Employee>("SELECT * FROM Employees")
                    .ToList();
}

Pass parameters to the query or stored procedure

public Employee GetEmployee(int id)
{
    return context.Employees
                    .FromSqlRaw<Employee>("spGetEmployeeById {0}", id)
                    .ToList()
                    .FirstOrDefault();
}

Passing multiple parameters to the query or stored procedure

FromSqlRaw(string sql, params object[] parameters)

The first parameter is the SQL to execute. The second parameter is the params array, so we can pass multiple parameters by separating them with a comma.

public Employee GetEmployee(int p1, int p2, int p3)
{
    return context.Employees
                    .FromSqlRaw<Employee>("spGetEmployeeById {0}, {1}, {2}", p1, p2, p3)
                    .ToList()
                    .FirstOrDefault();
}

Passing parameters using SqlParameter

public Employee GetEmployee(int id)
{
    SqlParameter parameter = new SqlParameter("@Id", id);

    return context.Employees
                    .FromSqlRaw<Employee>("spGetEmployeeById @Id", parameter)
                    .ToList()
                    .FirstOrDefault();
}

SqlParameter is in Microsoft.Data.SqlClient namespace

Use ExecuteSqlRaw to execute a SQL query or stored procedure that performs database operations but does not return entities. For example, if you want to execute insert, update or delete query or stored procedure use ExecuteSqlRaw method.

public Employee Add(Employee newEmployee)
{
    context.Database.ExecuteSqlRaw("spInsertEmployee {0}, {1}, {2}, {3}",
                                    newEmployee.Name,
                                    newEmployee.Email,
                                    newEmployee.PhotoPath,
                                    newEmployee.Department);
    return newEmployee;
}

Insert Stored procedure

Create Proc spInsertEmployee
@Name nvarchar(100),
@Email nvarchar(100),
@PhotoPath nvarchar(100),
@Dept int
AS
BEGIN

INSERT INTO Employees
    (Name, Email, PhotoPath, Department)
VALUES (@Name, @Email, @PhotoPath, @Dept)
END