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.