In an era where data management and efficiency are paramount, understanding and utilizing stored procedures within Entity Framework Core (EFCore) and SQLServer is more crucial than ever.
From the basics of their creation in SQL to their practical application in EFCore, this article aims to enhance your database management strategies, ensuring you leverage the full potential of stored procedures in your data-driven applications.
What is a stored procedure ?
A stored procedure is a set of SQL commands saved in the database catalog. Its primary purpose is to encapsulate complex operations into a single callable routine. Unlike individual SQL queries, stored procedures are compiled once and stored in executable form in the database, which boosts performance.
In the context of EFCore, stored procedures are particularly useful. They can bypass some of the limitations of EFCore's query generation, providing a more direct and efficient path to database operations. For example, when EFCore generates SQL from LINQ queries, the process can be opaque and sometimes less efficient than desired. Stored procedures offer a way to sidestep this by allowing direct execution of optimized SQL.
Consider the following table : Goat with Id, Name, Age, LastUpdate
Here's the SQL to create these tables:
CREATE TABLE Goat (
Id INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
LastUpdate DATETIME);Before getting to the heart of the matter of using stored procedures with EFCore, it's important to recall the basics of their creation in SQL.
Overview of stored procedures
Stored procedures have a defined structure using BEGIN and END keywords. For our Goat database, a simple procedure might look like this:
CREATE PROCEDURE [PROCEDURE_NAME]
AS
BEGIN
[QUERIES]
END;To return to the example, a simple data retrieval request has the following structure:
CREATE PROCEDURE GetGoats
AS
BEGIN
SELECT * FROM Goat;
END;It is possible to parameterize a procedure by defining variables that can be used within SQL queries to produce the following structure:
CREATE PROCEDURE [PROCEDURE_NAME] (@[PARAM_NAME] [PARAM_TYPE])The number of parameters is not finite, and can be used within procedure queries. Don't forget the @ symbol in front of the variable name.
CREATE PROCEDURE UpdateGoatName (
@GoatId INT,
@NewName VARCHAR(100))
AS
BEGIN
UPDATE Goat SET Name = @NewName WHERE Id = @GoatId;
END;In addition to parameters, SQL allows you to manage temporary variables to store values relating to a query. One of the advantages of stored procedures is their ability to chain SQL queries, either to make them depend on each other, or to ensure that they are executed as a block (this doesn't dispense with COMMIT and ROLLBACK, of course).
The AppendAgeToName procedure lets you add the goat's age to its name. To do this, first retrieve the age associated with the goat and store it in a variable, giving :
CREATE PROCEDURE AppendAgeToName (@GoatId INT)
AS
BEGIN
DECLARE @Age INT;
SELECT @Age = Age FROM Goat WHERE Id = @GoatId;
UPDATE Goat SET Name = Name + CAST(@Age AS VARCHAR(10)) WHERE Id = @GoatId;
END;Once the procedure has been created, all you need to do is execute it in SQL using the EXECUTE command:
EXECUTE GetGoats;Now that we've reviewed the basics, let's get down to business.
Use stored procedures with EFCore
EFCore has capabilities to efficiently handle stored procedures, making them a crucial part of the modern data access layer.
It is used in 2 ways: reading and writing.
Read
Simple case
Let's take the GetGoats stored procedure.
CREATE PROCEDURE GetGoats
AS
BEGIN
SELECT * FROM Goat;
END;For EFCore to be able to transform a stored procedure return into an object, it must be specified. The easiest way to do this is to use the FromSql method available on the DbSet.
In our case, the GetGoats procedure returns the entire Goat table associated with a Goat entity. Perfect!
By specifying the DbSet, EFCore is able to transform the return of the stored procedure into the type of the DbSet.
var goats = context.Goats
.FromSql("EXEC GetAllGoats")
.ToList();This is a special case where the content of the procedure returns all the columns of a table, but what about when you only want to retrieve certain information?
Complex case
Here, things get a little more complex.
There are 2 possibilities: create a view or use the SqlQuery method on the Database of the DbContext.
In both cases, you'll need to create an object that corresponds exactly to the returns of the procedure. The difference lies in the way this object is declared.
CREATE PROCEDURE GetNameAndLastUpdateGoats
AS
BEGIN
SELECT Name, LastUpdate FROM Goat;
END;For performance reasons, only the Name and LastUpdate fields are required for our stored procedure. The return object is therefore no longer a Goat, and for this we'll need to create the GoatInfoUpdate object:
public class GoatInfoUpdate
{
public string Name { get; set; }
public DateTime LastUpdate { get; set; }
}Let's start with the views !
View creation is the oldest technique, and has been used since the introduction of procedures. Use is similar to that of a real entity, using the DbSet of the GoatInfoUpdate object.
var goats = context.Set<GoatInfoUpdate>()
.FromSql("EXEC GetNameAndLastUpdateGoats")
.ToList();The declaration must be made in the OnModelCreating function of the DbContext, specifying in our case that the view has no primary key.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
.....
modelBuilder.Entity<GoatInfoUpdate>()
.HasNoKey()
.ToView("GoatInfoUpdate"); // It's a conceptual view, not a database view.
}Using SqlQuery is even simpler.
Introduced with EFCore 7, it could only retrieve primitive types (string, bool,...). Since EFCore 8, it has been possible to retrieve complex objects comprising a set of primitive types.
var goats = context.Database
.SqlQuery<GoatInfoUpdate>("EXEC GetNameAndLastUpdateGoats")
.ToList();There's no need to map the type to EFCore, which can resolve the return directly in the object.
One might well ask, then, what's the point of using views at all, and it's a very good question.
It may have gone unnoticed, but during the presentation of SqlQuery, the subtlety is made on the ability to retrieve only a complex object composed of primitive types.
If, for a specific case, you want to retrieve columns saved in JSON which have an associated complex type, it won't be possible to do so with SqlQuery, at least on the current version of EFCore 8.
To find out more, check out Microsoft's documentation:
Parametrized case
There's one last topic left to discuss: parameterization
The FromSql, FromSqlRaw and FromSqlInterpolated methods in Entity Framework Core (EFCore) are used to execute custom SQL queries and stored procedures. Although they appear similar, they have some key differences, especially in terms of security and ease of use.
In earlier versions of EF Core (prior to version 3.0), FromSql was used to execute custom SQL queries. However, this method has been replaced by FromSqlRaw and FromSqlInterpolated to clarify intentions and improve security.
The difference lies solely in the integration of the SqlParameter in the stored procedure call.
FromSqlRaw builds upstream SqlParameter
var goatId = 1;
var goatIdParam = new SqlParameter("@goatId", goatId);
var goats = context.Goats
.FromSqlRaw("EXEC MYPROCEDURE @goatId", goatIdParam)
.ToList();whereas FromSqlInterpolated generates them from the query
var goatId = 1;
var goats = context.Goats
.FromSqlInterpolated($"EXEC MYPROCEDURE {goatId}")
.ToList();FromSqlRaw is preferable when you need total control over parameter representation, while FromSqlInterpolated is more practical for fast, readable code writing.
You can create a more generic methods like this one to use it for all cases:
public async Task<List<T>> QueryProcedure<T>(string procedureName, CancellationToken cancellationToken = default, params SqlParameter[] sqlParameters)
where T : class
{
var query = $"EXEC {procedureName} {string.Join(", ", sqlParameters.Select(s => $"@{s.ParameterName}"))}";
return await dbContext.Set<T>()
.FromSqlRaw(query, sqlParameters as object[])
.ToListAsync(cancellationToken);
}Write
Writing via stored procedures is simpler than reading. It consists in making modifications in the database rather than retrieving data, which avoids having to manage the whole mapping part between returns and objects.
In this way, there's no need to go through a DbSet, but directly via the Database with the ExecuteSql function:
context.Database.ExecuteSql("EXEC MYPROCEDURE");The declaration of the stored procedure in question is the same for both reading and writing, with the two methods ExecuteSqlRaw and ExecuteSqlInterpolated , identical to those of FromSql.
However, ExecuteSql still returns a value corresponding to the number of rows impacted by the procedure natively, so there's no need to manage.
Create stored procedures with Code-First
EFCore doesn't yet support the generation of migration files containing stored procedures, but at least it lets you store them in them.
It would be unimaginable not to use EFCore to create/modify SQL procedures and to do it by hand.
All you have to do is create or add your stored procedures to your migration scripts:
public partial class StoredProceduresTemplate : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
var getNameAndLastUpdateGoats = @$"
CREATE PROCEDURE GetNameAndLastUpdateGoats
AS
BEGIN
SELECT {nameof(GoatInfoUpdate.Name)}, {nameof(GoatInfoUpdate.LastUpdate)} FROM Goat;
END";
migrationBuilder.Sql(getNameAndLastUpdateGoats);
}
}Conclusion
In this article, we explored the integration of stored procedures with EFCore and SQLServer. Stored procedures offer efficient data handling and improved performance, especially when combined with EFCore's capabilities.
The idea is not to replace all your database accesses with stored procedures, but to use them wisely.
Stored procedures can be used to manage the execution of queries on a database governed by RLS user rights. As a procedure can be executed with a role, it is possible to bypass rules without granting access, which will be the subject of a future article.
By mastering these techniques, developers can enhance the robustness and efficiency of their data-driven applications.
To go further :
Have a goat day 🐐
Join the conversation.