Back to: ASP.NET Core Tutorials For Beginners and Professionals
Stored Procedures in Entity Framework Core (EF Core)
In this article, I will discuss Stored Procedures in Entity Framework Core (EF Core) with Examples. Please read our previous article discussing Disconnected Entity Graph in Entity Framework Core with Examples. At the end of this article, you will understand How to Perform database CRUD Operations using Stored Procedure in Entity Framework core.
Stored Procedures in Entity Framework Core:
Entity Framework Core (EF Core) is an Object-Relational Mapping (ORM) framework for .NET that allows us to work with databases using .NET objects. While EF Core primarily focuses on LINQ-based query capabilities and entity modeling, it also supports stored procedures.
Stored Procedures in Entity Framework Core provide a way to execute predefined SQL logic on the database server. This can benefit performance, especially for complex queries.
How to Call Stored Procedure in Entity Framework Core
Calling a Stored Procedure in Entity Framework Core can be done in a few different ways depending on whether your stored procedure returns data, performs an INSERT, UPDATE, DELETE, or executes a command. Below are methods to handle each of these scenarios:
Executing a Stored Procedure that Returns Data:
If your stored procedure returns data, you can execute the Stored Procedure using the FromSqlRaw or FromSqlInterpolated methods and map the results to entity types. Suppose you have a stored procedure named GetCustomers that returns a set of customers. You can call it like this:
var customers = context.Customers.FromSqlRaw("EXEC GetCustomers").ToList();
//Using interpolated syntax (which is safer from SQL injection):
var customers = context.Customers.FromSqlInterpolated($"EXEC GetCustomers").ToList();
Executing a Stored Procedure for INSERT, UPDATE, DELETE
For stored procedures that perform INSERT, UPDATE, DELETE, or other non-query operations, you can use the ExecuteSqlRaw or ExecuteSqlInterpolated method of the database object. Suppose you have a stored procedure named UpdateCustomer that updates a customer’s data:
context.Database.ExecuteSqlRaw("EXEC UpdateCustomer @CustomerId, @Name", parameters: new[] { customerIdParameter, nameParameter });
//Using interpolated syntax:
context.Database.ExecuteSqlInterpolated($"EXEC UpdateCustomer {customerId}, {name}");
Stored Procedure with Output Parameters
If your stored procedure has output parameters, you must set Parameter Direction as Output and execute the Stored Procedure as follows.
//Input Parameter
var customerIdParameter = new SqlParameter("CustomerId", 1);
//Output Parameter
var customerNameParameter = new SqlParameter
{
ParameterName = "CustomerName",
SqlDbType = SqlDbType.VarChar,
Direction = ParameterDirection.Output,
Size = 50
};
context.Database.ExecuteSqlRaw("EXEC GetCustomerName @CustomerId, @CustomerName OUTPUT", customerIdParameter, customerNameParameter);
var customerName = customerNameParameter.Value.ToString();
Key Points To Remember While Working With EF Core Stored Procedure:
- SQL Injection: When using FromSqlRaw or ExecuteSqlRaw, be careful about SQL injection risks. Prefer FromSqlInterpolated and ExecuteSqlInterpolated for parameterized queries.
- Mapping Results: The column names returned by the stored procedure must match the property names of the entity type for correct mapping.
- Tracking: Queries using FromSqlRaw or FromSqlInterpolated that return entity types are tracked by default, meaning EF Core will try to keep track of changes to those entities. If you don’t need this, you can use AsNoTracking.
- No Mapping to Entities: If the results don’t map to entities, you can use ad-hoc types (DTOs) to capture the result set.
Example to Understand How to Call Stored Procedure in Entity Framework Core:
Let us proceed and understand this step by step. First, create the following Student Entity in our project.
namespace EFCoreCodeFirstDemo.Entities
{
public class Student
{
public int StudentId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Branch { get; set; }
public string Gender { get; set; }
}
}
Next, modify the Context class as follows:
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace EFCoreCodeFirstDemo.Entities
{
public class EFCoreDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
//To Display the Generated SQL Statements
//optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);
//Configuring the Connection String
optionsBuilder.UseSqlServer(@"Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=EFCoreDB;Trusted_Connection=True;TrustServerCertificate=True;");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
}
public DbSet<Student> Students { get; set; }
}
}
With the above changes, open the Package Manager Console and Execute the add-migration and update-database commands as follows. You can give any name to your migration. Here, I am giving EFCoreDBMig1. The name that you are giving it should not be given earlier.
Now, the database should be created with the Students database table as shown in the below image:
Creating Stored Procedures:
Create the following stored procedures to perform CRUD Operations with the Students database table.
Stored Procedure for Inserting a Student:
The following Stored Procedure will take the First Name, Last Name, Gender, and Branch as input parameters and the Student ID as an output parameter, then INSERT the student data into the Student database table. Then, it will return the newly inserted StudentId using the SCOPE_IDENTITY() function via the output parameter.
-- Insert Student Stored Procedure
CREATE PROCEDURE spInsertStudent
@FirstName VARCHAR(100),
@LastName VARCHAR(100),
@Branch VARCHAR(100),
@Gender VARCHAR(100),
@StudentId int OUTPUT
AS
BEGIN
INSERT INTO Students(FirstName ,LastName, Branch, Gender)
VALUES(@FirstName, @LastName, @Branch, @Gender);
SELECT @StudentId = SCOPE_IDENTITY()
END
Stored Procedure for Updating an Existing Student:
The following Stored Procedure will take the StudentId, First Name, Last Name, Gender, and Branch as input parameters and then update the First Name, Last Name, Gender, and Branch data into the Student database table based on the StudentId. This method does not return anything.
-- Update Student Stored Procedure
CREATE PROCEDURE spUpdateStudent
@StudentId INT,
@FirstName VARCHAR(100),
@LastName VARCHAR(100),
@Branch VARCHAR(100),
@Gender VARCHAR(100)
AS
BEGIN
UPDATE Students
SET FirstName = @FirstName,
LastName = @LastName,
Branch = @Branch,
Gender = @Gender
WHERE StudentId = @StudentId;
END
Stored Procedure for Delete an Existing Student:
The following Stored Procedure is used to Delete an existing Student from the Student Database Table based on the StudentId.
-- Delete Student Stored Procedure
CREATE PROCEDURE spDeleteStudent
@StudentId int
AS
BEGIN
DELETE FROM Students WHERE StudentId = @StudentId
END
Stored Procedure to Fetch All Students:
The following Stored Procedure fetches all Student data from the Student Database Table.
-- Get All Student Stored Procedure
CREATE PROCEDURE spGetAllStudents
AS
BEGIN
SELECT StudentId, FirstName, LastName, Branch, Gender
FROM Students;
END
Stored Procedure to Fetch Student by Student ID:
The following Stored Procedure is used to fetch a particular Student data from the Student Database Table based on the StudentId.
-- Get Student by Student Id Stored Procedure
CREATE PROCEDURE spGetStudentByStudentId
@StudentId INT
AS
BEGIN
SELECT StudentId, FirstName, LastName, Branch, Gender
FROM Students
WHERE StudentId = @StudentId;
END
How to Call Stored Procedure in Entity Framework Core?
To Call a Stored Procedure in Entity Framework Core, we need to use the following two methods:
- ExecuteSqlRaw/ExecuteSqlInterpolated: In Entity Framework Core (EF Core), the ExecuteSqlRaw or ExecuteSqlInterpolated methods executes raw SQL queries or Stored Procedure against the database. The ExecuteSqlRaw or ExecuteSqlInterpolated Methods are typically used for executing SQL commands or Stored Procedures that modify the database or for executing scalar queries that return a single value.
- FromSqlRaw/FromSqlInterpolated: In Entity Framework Core (EF Core), the FromSqlRaw or FromSqlInterpolated methods execute Raw-SQL Queries and Stored Procedure and map the retrieved data to entity objects. This can be useful when we need to perform complex queries or access database features that are not easily expressible using standard LINQ-to-Entities queries.
Example to Understand How to Call Stored Procedure with EF Core:
In Entity Framework Core (EF Core), we can work with stored procedures to perform CRUD (Create, Read, Update, Delete) operations on our database. Let us see how to perform these operations using stored procedures in EF Core. For a better understanding, please modify the Program class as follows. In the example below, we use ExecuteSqlRaw and FromSqlRaw Methods to call the Stored Procedures using EF Core.
using EFCoreCodeFirstDemo.Entities;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Data;
namespace EFCoreCodeFirstDemo
{
public class Program
{
static async Task Main(string[] args)
{
try
{
Student student1 = new Student()
{
FirstName = "Pranaya",
LastName = "Rout",
Branch = "CSE",
Gender = "Male"
};
Student student2 = new Student()
{
FirstName = "Hina",
LastName = "Sharma",
Branch = "CSE",
Gender = "Female"
};
//Call the AddStudent Method to add a new Student into the Database
int Id1 = AddStudent(student1);
Console.WriteLine($"Newly Added Student Id: {Id1}");
int Id2 = AddStudent(student2);
Console.WriteLine($"Newly Added Student Id: {Id2}");
//Call the GetStudentById Method to Retrieve a Single Student from the Database
var student = GetStudentById(1);
Console.WriteLine("\nGetStudentById: 1");
Console.WriteLine($"Id:{student?.StudentId}, Name: {student?.FirstName} {student?.LastName}, Branch: {student?.Branch}, Gender:{student?.Gender}");
//Call the GetAllStudents Method to Retrieve All Students from the Database
List<Student> students = GetAllStudents() ?? new List<Student>();
Console.WriteLine("\nGetAllStudents");
foreach (var std in students)
{
Console.WriteLine($"Id:{student?.StudentId}, Name: {student?.FirstName} {student?.LastName}, Branch: {student?.Branch}, Gender:{student?.Gender}");
}
//Let us Update the Student Whose Id = 1 i.e.student object
if (student != null)
{
student.FirstName = "Prateek";
student.LastName = "Sahoo";
//Call the UpdateStudent Method to Update an Existing Student in the Database
UpdateStudent(student);
student = GetStudentById(student.StudentId);
Console.WriteLine("After Updation");
Console.WriteLine($"Id:{student?.StudentId}, Name: {student?.FirstName} {student?.LastName}, Branch: {student?.Branch}, Gender:{student?.Gender}");
}
//Let us Delete the Student Whose Id = 1 i.e. student object
if (student != null)
{
DeleteStudent(student.StudentId);
}
Console.Read();
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
//The following Method is going to add a New Student into the database
//And return the new StudentId
public static int AddStudent(Student student)
{
int result = 0;
try
{
using var context = new EFCoreDbContext();
var FirstNameParam = new SqlParameter("@FirstName", SqlDbType.NVarChar)
{
Value = student.FirstName
};
var LastNameParam = new SqlParameter("@LastName", SqlDbType.NVarChar)
{
Value = student.LastName
};
var BranchParam = new SqlParameter("@Branch", SqlDbType.NVarChar)
{
Value = student.Branch
};
var GenderParam = new SqlParameter("@Gender", SqlDbType.NVarChar)
{
Value = student.Gender
};
var StudentIdOutParam = new SqlParameter("@StudentId", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
int NumberOfRowsAffected = context.Database.ExecuteSqlRaw("EXEC spInsertStudent @FirstName, @LastName, @Branch, @Gender, @StudentId OUTPUT",
FirstNameParam, LastNameParam, BranchParam, GenderParam, StudentIdOutParam);
if (NumberOfRowsAffected > 0)
{
// Retrieve the value of the OUT parameter
result = (int) StudentIdOutParam.Value;
}
}
catch (Exception ex)
{
Console.WriteLine($"AddStudent: Error Occurred: {ex.Message}");
}
return result;
}
//The following Method is going to return a Single Student from the database based on Student Id
//And return the new StudentId
public static Student? GetStudentById(int StudentId)
{
Student? student = null;
try
{
using var context = new EFCoreDbContext();
var StudentIdParam = new SqlParameter("@StudentId", SqlDbType.Int)
{
Value = StudentId
};
//Returning A Single Student Entity
//Execute the non-composable part of your query first using FromSqlRaw or SqlQuery, and
//materialize the results by calling ToList, ToArray, or AsEnumerable to ensure the data is retrieved from the database
var result = context.Students
.FromSqlRaw("EXEC spGetStudentByStudentId @StudentId", StudentIdParam)
.ToList(); //Materialize the result
if(result.Count > 0)
{
student = result.FirstOrDefault();
}
}
catch (Exception ex)
{
Console.WriteLine($"GetStudentById: Error Occurred: {ex.Message}");
}
return student;
}
//The following Method is going to return all Students from the database
public static List<Student>? GetAllStudents()
{
List<Student>? students = new List<Student>();
try
{
using var context = new EFCoreDbContext();
//Returning All Students
students = context.Students
.FromSqlRaw("EXEC spGetAllStudents")
.ToList();
}
catch (Exception ex)
{
Console.WriteLine($"GetAllStudents: Error Occurred: {ex.Message}");
}
return students;
}
//The following Method is going to Update an Existing Student into the database
public static void UpdateStudent(Student student)
{
try
{
using var context = new EFCoreDbContext();
var StudentIdParam = new SqlParameter("@StudentId", SqlDbType.Int)
{
Value = student.StudentId
};
var FirstNameParam = new SqlParameter("@FirstName", SqlDbType.NVarChar)
{
Value = student.FirstName
};
var LastNameParam = new SqlParameter("@LastName", SqlDbType.NVarChar)
{
Value = student.LastName
};
var BranchParam = new SqlParameter("@Branch", SqlDbType.NVarChar)
{
Value = student.Branch
};
var GenderParam = new SqlParameter("@Gender", SqlDbType.NVarChar)
{
Value = student.Gender
};
//result: Returns the number of rows affected
var result = context.Database.ExecuteSqlRaw("EXEC spUpdateStudent @StudentId, @FirstName , @LastName, @Branch, @Gender",
StudentIdParam, FirstNameParam, LastNameParam, BranchParam, GenderParam);
if (result > 0)
{
Console.WriteLine($"\nEntity with StudentId: {student.StudentId} Updated in the Database");
}
else
{
Console.WriteLine($"\nEntity with StudentId: {student.StudentId} Not Updated in the Database");
}
}
catch (Exception ex)
{
Console.WriteLine($"AddStudent: Error Occurred: {ex.Message}");
}
}
//The following Method is going to Delete an Existing Student from the database based on Student Id
//And return the new StudentId
public static Student? DeleteStudent(int StudentId)
{
Student? student = null;
try
{
using var context = new EFCoreDbContext();
var StudentIdParam = new SqlParameter("@StudentId", SqlDbType.Int)
{
Value = StudentId
};
//Deleting an Existing Student Entity
//result: Returns the number of rows affected
var result = context.Database.ExecuteSqlRaw("EXEC spDeleteStudent @StudentId", StudentIdParam);
if(result > 0)
{
Console.WriteLine($"\nEntity with StudentId: {StudentId} Deleted from the Database");
}
else
{
Console.WriteLine($"\nEntity with StudentId: {StudentId} Not Deleted from the Database");
}
}
catch (Exception ex)
{
Console.WriteLine($"DeleteStudent: Error Occurred: {ex.Message}");
}
return student;
}
}
}
Output:
ExecuteSqlRaw vs. ExecuteSqlInterpolated in EF Core
Entity Framework Core (EF Core) has two common methods for executing raw SQL queries: ExecuteSqlRaw and ExecuteSqlInterpolated. Both methods allow us to execute raw SQL commands directly against the database but differ in their handling of parameters.
ExecuteSqlRaw:
- Usage: This method allows you to execute a raw SQL query by passing the query string and parameters separately. It’s similar to the traditional SqlCommand execution in ADO.NET.
- Parameters: You need to pass the parameters explicitly. This means the query string contains placeholders for parameters, and you must provide the parameter values separately.
- Security: Since the parameters are passed separately from the query string, it helps prevent SQL injection attacks. However, it’s still important to be cautious and avoid concatenating user inputs directly into the query string.
ExecuteSqlInterpolated:
- Usage: This method allows you to execute a SQL query using interpolated string syntax, which makes the code more readable and concise.
- Parameters: It supports C# interpolated strings, where you can embed the parameters directly into the SQL query string. EF Core will automatically parameterize these values to prevent SQL injection.
- Security: It’s generally safe from SQL injection attacks as EF Core converts the interpolated values into parameters.
Choosing Between ExecuteSqlRaw and ExecuteSqlInterpolated:
- Readability: ExecuteSqlInterpolated often leads to more readable code due to using interpolated strings.
- Safety: Both methods are safe from SQL injection when used properly. However, ExecuteSqlInterpolated might be more prone to misuse if developers aren’t careful about directly embedding user input into the query string.
- Preference: The choice can also depend on personal or team preference and coding standards.
FromSqlRaw vs. FromSqlInterpolated in EF Core
In Entity Framework Core (EF Core), FromSqlRaw and FromSqlInterpolated are methods used to execute raw SQL queries and map the results to entities or other types. Both serve a similar purpose but differ in handling SQL queries and parameters.
FromSqlRaw:
- Usage: This method is used to execute raw SQL queries. You must pass the SQL query as a string and any parameters as separate arguments.
- Parameters: Parameters are passed explicitly. The query string contains placeholders (like {0}, {1}, etc.), and the parameters are provided as additional arguments.
- Security: FromSqlRaw is safe from SQL injection if parameters are used correctly and not concatenated into the query string. Always use placeholders for parameters instead of concatenating or interpolating user input directly into the SQL string.
FromSqlInterpolated:
- Usage: This method allows us to write SQL queries using interpolated string syntax, making the code more readable.
- Parameters: It supports C# interpolated strings, enabling you to embed parameters directly within the SQL query string. EF Core automatically parameterizes these values to prevent SQL injection.
- Security: FromSqlInterpolated is designed to be safe from SQL injection attacks as it converts interpolated values into parameters.
Choosing Between FromSqlRaw and FromSqlInterpolated:
- Readability: FromSqlInterpolated uses interpolated strings, which can be more readable and concise. FromSqlRaw requires explicit parameter passing, which may be less concise but clearer in some cases.
- Safety: Both methods are safe from SQL injection when used correctly. However, FromSqlInterpolated might be easier to misuse if developers embed user inputs directly into the SQL string.
- Use Case: The choice between these methods often comes down to personal preference, coding standards, and the specific context in which they are used.
Example Using ExecuteSqlInterpolated and FromSqlInterpolated Methods in EF Core
In our previous example, we used the ExecuteSqlRaw and FromSqlRaw methods to call the Stored Procedures. Let us rewrite the same example using ExecuteSqlInterpolated and FromSqlInterpolated Methods in EF Core to call the Stored Procedures. For a better understanding, please look at the below example.
using EFCoreCodeFirstDemo.Entities;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Data;
namespace EFCoreCodeFirstDemo
{
public class Program
{
static async Task Main(string[] args)
{
try
{
Student student1 = new Student()
{
FirstName = "Pranaya",
LastName = "Rout",
Branch = "CSE",
Gender = "Male"
};
Student student2 = new Student()
{
FirstName = "Hina",
LastName = "Sharma",
Branch = "CSE",
Gender = "Female"
};
//Call the AddStudent Method to add a new Student into the Database
int Id1 = AddStudent(student1);
Console.WriteLine($"Newly Added Student Id: {Id1}");
int Id2 = AddStudent(student2);
Console.WriteLine($"Newly Added Student Id: {Id2}");
//Call the GetStudentById Method to Retrieve a Single Student from the Database
var student = GetStudentById(1);
Console.WriteLine("\nGetStudentById: 1");
Console.WriteLine($"Id:{student?.StudentId}, Name: {student?.FirstName} {student?.LastName}, Branch: {student?.Branch}, Gender:{student?.Gender}");
//Call the GetAllStudents Method to Retrieve All Students from the Database
List<Student> students = GetAllStudents() ?? new List<Student>();
Console.WriteLine("\nGetAllStudents");
foreach (var std in students)
{
Console.WriteLine($"Id:{student?.StudentId}, Name: {student?.FirstName} {student?.LastName}, Branch: {student?.Branch}, Gender:{student?.Gender}");
}
//Let us Update the Student Whose Id = 1 i.e.student object
if (student != null)
{
student.FirstName = "Prateek";
student.LastName = "Sahoo";
//Call the UpdateStudent Method to Update an Existing Student in the Database
UpdateStudent(student);
student = GetStudentById(student.StudentId);
Console.WriteLine("After Updation");
Console.WriteLine($"Id:{student?.StudentId}, Name: {student?.FirstName} {student?.LastName}, Branch: {student?.Branch}, Gender:{student?.Gender}");
}
//Let us Delete the Student Whose Id = 1 i.e. student object
if (student != null)
{
DeleteStudent(student.StudentId);
}
Console.Read();
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
//The following Method is going to add a New Student into the database
//And return the new StudentId
public static int AddStudent(Student student)
{
int result = 0;
try
{
using var context = new EFCoreDbContext();
var FirstNameParam = new SqlParameter("FirstName", student.FirstName);
var LastNameParam = new SqlParameter("LastName", student.LastName);
var BranchParam = new SqlParameter("Branch", student.Branch);
var GenderParam = new SqlParameter("Gender", student.Gender);
var StudentIdOutParam = new SqlParameter
{
ParameterName = "StudentId",
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.Output
};
int NumberOfRowsAffected = context.Database.ExecuteSqlInterpolated($"EXEC spInsertStudent @FirstName={FirstNameParam}, @LastName={LastNameParam}, @Branch={BranchParam}, @Gender={GenderParam}, @StudentId={StudentIdOutParam} OUTPUT");
if (NumberOfRowsAffected > 0)
{
// Retrieve the value of the OUT parameter
result = (int)StudentIdOutParam.Value;
}
}
catch (Exception ex)
{
Console.WriteLine($"AddStudent: Error Occurred: {ex.Message}");
}
return result;
}
//The following Method is going to return a Single Student from the database based on Student Id
//And return the new StudentId
public static Student? GetStudentById(int StudentId)
{
Student? student = null;
try
{
using var context = new EFCoreDbContext();
var result = context.Students
.FromSqlInterpolated($"EXEC spGetStudentByStudentId @StudentId={StudentId}")
.ToList();
if (result.Count > 0)
{
student = result.FirstOrDefault();
}
}
catch (Exception ex)
{
Console.WriteLine($"GetStudentById: Error Occurred: {ex.Message}");
}
return student;
}
//The following Method is going to return all Students from the database
public static List<Student>? GetAllStudents()
{
List<Student>? students = new List<Student>();
try
{
using var context = new EFCoreDbContext();
//Returning All Students
students = context.Students
.FromSqlInterpolated($"EXEC spGetAllStudents")
.ToList();
}
catch (Exception ex)
{
Console.WriteLine($"GetAllStudents: Error Occurred: {ex.Message}");
}
return students;
}
//The following Method is going to Update an Existing Student into the database
public static void UpdateStudent(Student student)
{
try
{
using var context = new EFCoreDbContext();
var StudentIdParam = new SqlParameter("StudentId", student.StudentId);
var FirstNameParam = new SqlParameter("FirstName", student.FirstName);
var LastNameParam = new SqlParameter("LastName", student.LastName);
var BranchParam = new SqlParameter("Branch", student.Branch);
var GenderParam = new SqlParameter("Gender", student.Gender);
//result: Returns the number of rows affected
var result = context.Database.ExecuteSqlInterpolated($"EXEC spUpdateStudent @StudentId={StudentIdParam}, @FirstName={FirstNameParam}, @LastName={LastNameParam}, @Branch={BranchParam}, @Gender={GenderParam}");
if (result > 0)
{
Console.WriteLine($"\nEntity with StudentId: {student.StudentId} Updated in the Database");
}
else
{
Console.WriteLine($"\nEntity with StudentId: {student.StudentId} Not Updated in the Database");
}
}
catch (Exception ex)
{
Console.WriteLine($"AddStudent: Error Occurred: {ex.Message}");
}
}
//The following Method is going to Delete an Existing Student from the database based on Student Id
//And return the new StudentId
public static Student? DeleteStudent(int StudentId)
{
Student? student = null;
try
{
using var context = new EFCoreDbContext();
var StudentIdParam = new SqlParameter("StudentId", StudentId);
//Deleting an Existing Student Entity
//result: Returns the number of rows affected
var result = context.Database.ExecuteSqlInterpolated($"EXEC spDeleteStudent @StudentId={StudentIdParam}");
if (result > 0)
{
Console.WriteLine($"\nEntity with StudentId: {StudentId} Deleted from the Database");
}
else
{
Console.WriteLine($"\nEntity with StudentId: {StudentId} Not Deleted from the Database");
}
}
catch (Exception ex)
{
Console.WriteLine($"DeleteStudent: Error Occurred: {ex.Message}");
}
return student;
}
}
}
Considerations and Best Practices of Executing Stored Procedure Using EF Core
- Performance: Stored procedures can sometimes offer performance advantages, especially for complex operations, as they are executed on the server side.
- Maintainability: While stored procedures encapsulate logic within the database, which can be beneficial, they also create an additional layer of code to maintain outside your application.
- Portability: Relying on stored procedures can reduce the portability of your application across different database systems, as stored procedures are often specific to a particular SQL dialect.
- Debugging: Debugging issues can be more challenging with stored procedures since the logic is executed outside the EF Core and .NET environments.
- Compatibility: Ensure that your version of Entity Framework Core supports the features you need for working with stored procedures, as EF Core’s support for stored procedures has evolved over time.
Differences Between ExecuteSqlRaw and FromSqlRaw in Entity Framework Core
In Entity Framework, ExecuteSqlRaw and FromSqlRaw are two methods that allow us to execute raw SQL queries or Stored Procedures against the database. They have some key differences in terms of their usage and the results they return:
Purpose:
- FromSqlRaw: This method maps the results of a RAW SQL Query or Stored Procedure to an entity type. It allows you to retrieve data from the database and map it to entity objects. So, if you want to retrieve table data (entities or complex types) from the database, you need to use the FromSqlRaw Method.
- ExecuteSqlRaw: This method executes RAW SQL Queries or Stored Procedures that do not return entities or complex types. It is typically used for executing SQL commands that modify the database (e.g., INSERT, UPDATE, DELETE) or return scalar values (e.g., COUNT, SUM).
Return Type:
- FromSqlRaw: It returns an IQueryable<TEntity> or DbSet<TEntity>, where TEntity is an entity type we need to specify. This allows us to work with the results as regular entity objects. If you are returning a single entity, you need to use DbSet<TEntity>, and if you are returning multiple entities, then you need to use IQueryable<TEntity>.
- ExecuteSqlRaw: It does not return data as entities. Instead, it typically returns the number of rows affected by the query or, in some cases, the result of a scalar query.
Usage:
- FromSqlRaw: Typically used for querying data from the database using Raw SQL or Stored Procedure and then mapping the results to entity objects. For example, you can use it to retrieve a list of entities from the database based on some condition or without any condition.
- ExecuteSqlRaw: It is typically used for executing RAW SQL Commands or Stored Procedures that don’t return entity objects. It is mainly used to Perform INSERT, UPDATE, and DELETE operations or for executing scalar queries to retrieve a single value.
Safety:
- FromSqlRaw: Entity Framework Core will attempt to map the query results to the specified entity type, so we should use it when we expect the query to return data that can be mapped to the entity. Incorrect mappings can lead to runtime errors.
- ExecuteSqlRaw: It is safer for executing non-query SQL commands because it doesn’t involve mapping to entities. However, you must still be cautious with user inputs and ensure they are correctly parameterized to prevent SQL injection.
So, FromSqlRaw is used for querying and mapping data to entity objects, while ExecuteSqlRaw is used for executing raw SQL commands and scalar queries that do not return entities. Both methods have their own specific use cases and should be chosen based on the task you want to perform.
In the next article, I will discuss Entity Framework Core (EF Core) Inheritance (TPH, TPT, and TPC) with Examples. In this article, I try to explain Stored Procedures in Entity Framework Core with Examples. I hope you enjoyed this Stored Procedures in EF Core article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.
