Using stored procedure in entity framework core

Suggested Videos
Part 29 - Using sql server in razor pages project | Text | Slides
Part 30 - EF core migrations in razor pages project | Text | Slides
Part 31 - Repository pattern in asp.net core razor pages | Text | Slides

In this video we will discuss how to use stored procedures with entity framework core.


EF Core FromSqlRaw Method

FromSqlRaw() is one of the methods we can use to execute stored procedures from entity framework core. This method is also useful if the query can't be expressed using LINQ or when LINQ is not able to generate efficient SQL query.


Creating stored procedure with database first approach

Create Procedure spGetEmployeeById
@Id int
as
Begin
Select * from Employees
Where Id = @Id
End

This procedures gets employee by id. You can create this procedure manually using SQL Server management studio, if you are using database first approach. 

Creating stored procedure with code fist approach

If you are using code first approach we want this stored procedure to be created via the entity framework core migration.

In the Package Manager Console, execute the following command

Add-Migration spGetEmployeeById

In the generated .cs migration file, include the following code in the Up() method

protected override void Up(MigrationBuilder migrationBuilder)
{
    string procedure = @"Create Procedure spGetEmployeeById
                            @Id int
                            as
                            Begin
                            Select * from Employees
                            Where Id = @Id
                            End";
    migrationBuilder.Sql(procedure);
}

Include the following code in the Down() method

protected override void Down(MigrationBuilder migrationBuilder)
{
    string procedure = @"Drop procedure spGetEmployeeById";
    migrationBuilder.Sql(procedure);
}

Execute the migration, using the following command. This creates the stored procedure in the database.

Update-Database

Call Stored Procedure using FromSqlRaw

{0} is the parameter placeholder. While this syntax may look like String.Format syntax, the supplied value is treated like a parameter value and is not vulnerable to SQL injection.

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