Performing Complicated Entity Structure Core Stored Procedures

Internet designers frequently require to call a database kept treatment (SP) from their C# server layer. Microsoft’s Entity Structure (EF) Core can be utilized to map or import SPs as functions however, regrettably, EF Core does not natively support the retrieval of complicated arise from kept treatments. This is because of constraints in EF Core’s out-of-the-box service that:

• Limit a kept treatment’s outcome to an Entity type.
• Can not return an intricate key in action to a SIGN UP WITH command.
• Make produce, upgrade, and erase operations not available.

We can navigate these limitations by utilizing C#, WEB, Microsoft SQL Server, and EF Core together. This workaround can be utilized with any.NET-supported database or.NET language that supports EF Core, offered the energy code is equated into that language. We’ll take a look at an example kept treatment to see how a couple of easy changes can get rid of EF Core’s restraints.

A Theoretical Stored Treatment With a Complex Outcome

Let’s think about GetEmployeesWithDepartment, a kept treatment that returns an intricate outcome consisting of info from 2 associated database tables, Staff Member and Department:

The Staff Member table referrals itself through a foreign secret from its ManagerId field. It likewise referrals the Department table from the Employee.DepartmentId field linked to the Department table’s Id column. The ordinal relationships in between these tables are:

 Relationships = Staff Member( 1 ): Department( 1) and Department( 1 ): Workers( N).

Now let’s take a look at GetEmployeesWithDepartment, an SP that returns an Staff Member table row matching the input criterion Employee.Id Our SP returns the Id worth and all of its associated info, such as the worker’s Department and Call worths:

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

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

Let’s state we wish to identify the department related to the very first worker noted in a easy test database (in our example, the very first worker noted is John in Engineering). We wish to perform this SP from our C# code, so let’s set up EF Core to support calling GetEmployeesWithDepartment as a parameterized SP.

Note: Prior to you continue, scaffold your database utilizing the Scaffold-DbContext command in the Plan Supervisor Console or the dotnet ef dbcontext scaffold command in.NET Core CLI.

Action 1: Produce a Stored Treatment Outcome Set Design

Initially, we’ll produce a file called GetEmployeesWithDepartment_Result. cs and specify the structure for our complex return type:

 public class GetEmployeesWithDepartment_Result.
{
public int Id {get; set;}
public string Call {get; set;}
public int DepartmentId {get; set;}
public int? ManagerId {get; set;}
public int Wage {get; set;}
public decimal? Reward {get; set;}
public string Department {get; set;}
}

Utilizing Microsoft SQL Server as the database server, we can clearly validate the SP result column types by carrying out the sp_describe_first_result_set command:

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

This command shows the kept treatment’s columns and associated type list. With the outcome type specified, we carry on to upgrading our EF design

Action 2: Consist Of the Design in the DbContext File

We are prepared to integrate the outcome design into our application’s EF Core DbContext file. EF offers a classy technique to extending an application’s information design. Such an extension is supported with partial classes and– particularly– by utilizing an OnModelCreatingPartial approach. To keep EF Core’s scaffolding tools from customizing our custom-made code, we’ll include our outcome design to EFCoreSPContext.SP.cs, a partial C# class:

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

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

// We'll include subsequent modifications here.
}
}

Here’s how EFCoreSPContext.SP.cs searches in our repository We now require to include code that recognizes our design’s main secret, if one exists.

Action 3: Define the Secret of the Design

We’ll suggest whether our SP’s outcome set has a crucial worth by configuring our design in an OnModelCreatingPartial approach in our EFCoreSPContext meaning.

If our outcome set has a crucial worth, we utilize the HasKey approach to clearly recognize the residential or commercial property related to that essential worth:

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

If our entity has no essential worth, we utilize the HasNoKey approach rather:

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

Our design meaning is now total. We’re prepared to call the SP and obtain our example worker information.

Calling Complicated Stored Procedures: Easy As 1-2-3

To streamline calling our SP, we’ll include another public approach to the EFCoreSPContext file. The approach’s meaning accepts the Employee.Id worth offered, passes that Id to the SP, and obtains the created complex results as a list:

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

Our DbContext file is now prepared to call a kept treatment and return a complex type result set, and our code is total Going back to our example question, we can utilize a basic command to return the department and other information related to the very first worker in our database:

 var staff members = dbContext.SP _ GetEmployeesWithDepartment( 1 );.

We used a basic, yet creative and effective, service to return a non-database entity from a kept treatment. This technique requires reasonably couple of lines of supporting code and yields a substantial benefit when utilizing EF Core to obtain complicated outcomes.

The editorial group of the Toptal Engineering Blog site extends its appreciation to Alexander Skogorev for evaluating the technical material and code samples provided in this short article.