Discover proven strategies and practical solutions for efficiently handling stored procedures in Entity Framework Core (EFCore) and optimize your database interactions.
Entity Framework Core (EFCore) is a powerful tool for managing database migrations, but it doesn't natively support stored procedures. This limitation can be a challenge for developers who rely on stored procedures for performance optimization and encapsulating complex SQL logic.
Stored procedures are inherently sensitive to changes in the database schema. Unlike entities, there is no direct link between a stored procedure and the schema in EFCore, making them susceptible to becoming outdated. This necessitates a manual approach to managing stored procedures, which can be both time-consuming and error-prone. However, by adopting a structured approach, we can mitigate these issues and ensure our stored procedures remain functional and up-to-date.
In this article, we will explore how to manage stored procedures using EFCore, ensuring they remain robust and maintainable even as the database schema evolves.
If you haven't read it yet, I recommend that you first read our article on writing stored procedures before continuing to read this one, in order to fully understand the construction of stored procedures in EFCore.
Goat Review
Here we go!
Stored procedure management with EFCore
Unlike entities, it is not possible to generate a migration file for stored procedures, as they are not directly linked to EntityFramework Core. To do this, migration files containing the procedure code must be generated manually.
A simple rendering would be as follows:
public partial class StoredProcedures : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(AddGoat.CreateProcedureSql);
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(AddGoat.DropProcedureSql);
}
}To avoid making the migration file unreadable when adding a multitude of stored procedures, a simple way of managing them is to use classes, each corresponding to a procedure.
Each of these classes contains 2 methods: CreateProcedureSql to generate the SQL for creating the procedure, and DropProcedureSql to delete it.
Now that this has been done, we need to go a step further and make the SQL generation of procedures robust.
But why make stored procedures robust?
Making stored procedures robust
Stored procedures are highly case-sensitive and can become obsolete when the database schema changes. Unlike entities, there is no direct link between a stored procedure and the schema, hence the importance of creating this dependency.
For example, if you rename a table or column, all stored procedures that depend on these names must be updated.
By making stored procedures robust, we ensure that they remain functional and up-to-date, even when the schema evolves.
On modification procedures
To illustrate this, let's take the example of a stored procedure that adds a new goat to a table. We'll specify the table name and the field to be inserted via a list of constants, based on the entity.
internal static class AddGoat
{
internal static string CreateProcedureSql =>
@$"
CREATE OR ALTER PROCEDURE [{StoredProceduresConstants.AddGoat}] (
@{nameof(GoatEntity.Name)} NVarChar(128)
)
AS
BEGIN
INSERT INTO [{EntityTableNames.GOAT}] ([{nameof(GoatEntity.Name)}])
VALUES @{nameof(GoatEntity.Name)}
END
GO
";
}The procedure creates links between 3 elements: the entity, the table and the procedure name.
The first is on the entity on which it is based, by forcing field naming identical to that of the entity into which the data will be inserted. If the entity changes an element, the procedure script will block at compile time.
class GoatEntity
{
public required Guid Id { get; set; }
public required string Name { get; set; }
}The second is the table name, mainly for maintainability reasons. When a project contains more than thirty procedures, it's better to anticipate refactoring problems than to suffer them in the future.
public static class EntityTableNames
{
public const string GOAT = "Goat";
}The table name constant must then be used in entity configuration.
internal class GoatConfiguration : EntityBaseConfiguration<GoatEntity, int>
{
public override void Configure(EntityTypeBuilder<GoatEntity> entity)
{
base.Configure(entity);
entity.ToTable(EntityTableNames.GOAT);
...
}
}Finally, the procedure name is also in constant to link implementation to use.
public static class StoredProceduresConstants
{
public static readonly string AddGoat = "AddGoat";
}This gives us a totally magic string-free application.
unitOfWork.ExecuteProcedure(nameof(StoredProceduresConstants.AddGoat),
new SqlParameter(nameof(GoatEntity.Name), [VALUEHERE]));By using constants for table and column names, we ensure that stored procedures are automatically updated when the schema changes. This reduces the risk of errors and facilitates scripts maintenance.
On reading procedures
Read stored procedures return results in objects. Here's an example of a stored procedure that returns the name of a goat based on its identifier:
internal static class GetGoat
{
internal static string CreateProcedureSql =>
$"""
CREATE OR ALTER PROCEDURE {StoredProceduresConstants.GetGoat} (
@{nameof(GoatEntity.Id)} uniqueidentifier)
AS
BEGIN
SELECT [g].[{nameof(GoatEntity.Name)}] as {nameof(GoatNameResult.Value)},
FROM [{EntityTableNames.GOAT}] AS [g]
WHERE [g].[{nameof(GoatEntity.Id)}] = @{nameof(GoatEntity.Id)}
END
GO
""";
}By using the as on the nameof of GoatNameResult, we ensure that the return of the stored procedure matches the properties of the return object.
class GoatNameResult
{
public string Value { get; set; }
}It's important to match the name of the entity column with that of the return class, especially when the stored procedure returns several columns with the same name, as frequently happens with the Id column.
To delete the procedure
To ensure easy rollback of the procedure, it is important to implement a Drop method:
internal static class AddGoat
{
internal static string DropProcedureSql =>
@$"
DROP PROCEDURE [{StoredProceduresConstants.AddGoat}]
GO
";
}In this case, you don't need to go any further: the name of the procedure is enough.
Conclusion
In conclusion, managing stored procedures with EFCore requires a thoughtful and structured approach. By using constants for table and column names, we can create a robust link between our stored procedures and the database schema.
This ensures that our procedures remain functional and up-to-date, even as the schema evolves. Additionally, implementing methods for creating and dropping procedures simplifies the migration process and enhances maintainability.
Ultimately, the goal is to create a seamless integration between EFCore and stored procedures, leveraging the strengths of both to build efficient and maintainable applications.
Have a goat day 🐐
Join the conversation.