Saturday, November 21, 2026
HomeSoftware EngineeringExecuting Advanced Entity Framework Core Saved Procedures

Executing Advanced Entity Framework Core Saved Procedures

[ad_1]

.NET builders usually have to name a database saved process (SP) from their C# server layer. Microsoft’s Entity Framework (EF) Core can be utilized to map or import SPs as features however, sadly, EF Core doesn’t natively help the retrieval of advanced outcomes from saved procedures. This is because of limitations in EF Core’s out-of-the-box resolution that:

  • Limit a saved process’s consequence to an Entity sort.
  • Can not return a fancy sort in response to a JOIN command.
  • Make create, replace, and delete operations unavailable.

We will get round these restrictions through the use of C#, .NET, Microsoft SQL Server, and EF Core collectively. This workaround can be utilized with any .NET-supported database or .NET language that helps EF Core, supplied the utility code is translated into that language. We’ll take a look at an instance saved process to see how a couple of easy changes can overcome EF Core’s constraints.

A Hypothetical Saved Process With a Advanced End result

Let’s take into account GetEmployeesWithDepartment, a saved process that returns a fancy consequence containing data from two associated database tables, Worker and Division:

Two related database tables from which a stored procedure that returns a complex result containing information could come.

The Worker desk references itself by a international key from its ManagerId discipline. It additionally references the Division desk from the Worker.DepartmentId discipline linked to the Division desk’s Id column. The ordinal relationships between these tables are:

Relationships = Worker(1) : Division(1) and Division(1) : Staff(N)

Now let’s take a look at GetEmployeesWithDepartment, an SP that returns an Worker desk row matching the enter parameter Worker.Id. Our SP returns the Id worth and all of its related data, corresponding to the worker’s Division and Title values:

CREATE OR ALTER PROCEDURE [dbo].[GetEmployeesWithDepartment] 	
    @id INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT [E].*, [D].[Name] AS [Department]
    FROM [dbo].[Employee] [E]
        INNER JOIN [dbo].[Department] [D] ON [E].[DepartmentId] = [D].[Id]
    WHERE [E].[Id] >= @id
END

Let’s say we wish to decide the division related to the primary worker listed in a easy check database (in our instance, the primary worker listed is John in Engineering). We want to execute this SP from our C# code, so let’s configure EF Core to help calling GetEmployeesWithDepartment as a parameterized SP.

Be aware: Earlier than you proceed, scaffold your database utilizing the Scaffold-DbContext command within the Package deal Supervisor Console or the dotnet ef dbcontext scaffold command in .NET Core CLI.

Step 1: Create a Saved Process End result Set Mannequin

First, we’ll create a file referred to as GetEmployeesWithDepartment_Result.cs and outline the construction for our advanced return sort:

public class GetEmployeesWithDepartment_Result
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int DepartmentId { get; set; }
    public int? ManagerId { get; set; }
    public int Wage { get; set; }
    public decimal? Bonus { get; set; }
    public string Division { get; set; }
}

Utilizing Microsoft SQL Server because the database server, we will explicitly confirm the SP consequence column sorts by executing the sp_describe_first_result_set command:

EXEC sp_describe_first_result_set N'[dbo].[GetEmployeesWithDepartment]'

This command shows the saved process’s columns and related sort listing. With the consequence sort outlined, we transfer on to updating our EF mannequin.

Step 2: Embrace the Mannequin within the DbContext File

We’re prepared to include the consequence mannequin into our software’s EF Core DbContext file. EF gives a chic method to extending an software’s information mannequin. Such an extension is supported with partial lessons and—particularly—through the use of an OnModelCreatingPartial methodology. To maintain EF Core’s scaffolding instruments from modifying our customized code, we’ll add our consequence mannequin to EFCoreSPContext.SP.cs, a partial C# class:

utilizing EFCoreSP.Knowledge.SPs;
utilizing Microsoft.EntityFrameworkCore;
utilizing System.Collections.Generic;
utilizing System.Linq;

namespace EFCoreSP.Knowledge
{
    public partial class EFCoreSPContext : DbContext
    {
        public digital DbSet<GetEmployeesWithDepartment_Result>
            GetEmployeesWithDepartment_Results { get; set; }

        // We’ll add subsequent modifications right here
    }
}

Right here’s how EFCoreSPContext.SP.cs appears in our repository. We now want so as to add code that identifies our mannequin’s main key, if one is current.

Step 3: Specify the Key of the Mannequin

We’ll point out whether or not our SP’s consequence set has a key worth by configuring our mannequin in an OnModelCreatingPartial methodology in our EFCoreSPContext definition.

If our consequence set has a key worth, we use the HasKey methodology to explicitly establish the property related to that key worth:

partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<GetEmployeesWithDepartment_Result>(entity => 
        entity.HasKey(e => e.Id));      
}

If our entity has no key worth, we use the HasNoKey methodology as a substitute:

partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<GetEmployeesWithDepartment_Result>(entity => 
        entity.HasNoKey());       
}

Our mannequin definition is now full. We’re able to name the SP and retrieve our instance worker information.

Calling Advanced Saved Procedures: Straightforward As 1-2-3

To simplify calling our SP, we’ll add another public methodology to the EFCoreSPContext file. The tactic’s definition accepts the Worker.Id worth supplied, passes that Id to the SP, and retrieves the generated advanced outcomes as an inventory:

public IEnumerable<GetEmployeesWithDepartment_Result> 
    SP_GetEmployeesWithDepartment(int id)
{
    return this.GetEmployeesWithDepartment_Results
        .FromSqlInterpolated($"[dbo].[GetEmployeesWithDepartment] {id}")
        .ToArray();
}

Our DbContext file is now able to name a saved process and return a fancy sort consequence set, and our code is full. Returning to our instance question, we will use a easy command to return the division and different information related to the primary worker in our database:

var workers = dbContext.SP_GetEmployeesWithDepartment(1);

We utilized a easy, but intelligent and highly effective, resolution to return a non-database entity from a saved process. This method entails comparatively few traces of supporting code and yields a substantial payoff when utilizing EF Core to retrieve advanced outcomes.

The editorial crew of the Toptal Engineering Weblog extends its gratitude to Alexander Skogorev for reviewing the technical content material and code samples offered on this article.


[ad_2]

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments