Physical Address
304 North Cardinal St.
Dorchester Center, MA 02124
Physical Address
304 North Cardinal St.
Dorchester Center, MA 02124
.NET builders typically must 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 capabilities however, sadly, EF Core doesn’t natively help the retrieval of complicated outcomes from saved procedures. This is because of limitations in EF Core’s out-of-the-box resolution that:
Entity
kind.JOIN
command.We are able to 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, offered the utility code is translated into that language. We’ll have a look at an instance saved process to see how a couple of easy changes can overcome EF Core’s constraints.
Let’s contemplate GetEmployeesWithDepartment
, a saved process that returns a fancy end result containing info from two associated database tables, Worker
and Division
:
The Worker
desk references itself by a international key from its ManagerId
area. It additionally references the Division
desk from the Worker.DepartmentId
area linked to the Division
desk’s Id
column. The ordinal relationships between these tables are:
Relationships = Worker(1) : Division(1) and Division(1) : Workers(N)
Now let’s have 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 info, reminiscent of 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.
Observe: Earlier than you proceed, scaffold your database utilizing the Scaffold-DbContext
command within the Bundle Supervisor Console or the dotnet ef dbcontext scaffold
command in .NET Core CLI.
First, we’ll create a file known as GetEmployeesWithDepartment_Result.cs
and outline the construction for our complicated return kind:
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 are able to explicitly confirm the SP end result column varieties 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 kind listing. With the end result kind outlined, we transfer on to updating our EF mannequin.
DbContext
FileWe’re prepared to include the end result mannequin into our software’s EF Core DbContext
file. EF gives a sublime method to extending an software’s knowledge mannequin. Such an extension is supported with partial courses and—particularly—through the use of an OnModelCreatingPartial
technique. To maintain EF Core’s scaffolding instruments from modifying our customized code, we’ll add our end result mannequin to EFCoreSPContext.SP.cs
, a partial C# class:
utilizing EFCoreSP.Information.SPs;
utilizing Microsoft.EntityFrameworkCore;
utilizing System.Collections.Generic;
utilizing System.Linq;
namespace EFCoreSP.Information
{
public partial class EFCoreSPContext : DbContext
{
public digital DbSet<GetEmployeesWithDepartment_Result>
GetEmployeesWithDepartment_Results { get; set; }
// We’ll add subsequent adjustments right here
}
}
Right here’s how EFCoreSPContext.SP.cs
appears to be like in our repository. We now want so as to add code that identifies our mannequin’s main key, if one is current.
We’ll point out whether or not our SP’s end result set has a key worth by configuring our mannequin in an OnModelCreatingPartial
technique in our EFCoreSPContext
definition.
If our end result set has a key worth, we use the HasKey
technique to explicitly determine 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
technique 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 knowledge.
To simplify calling our SP, we’ll add yet another public technique to the EFCoreSPContext
file. The tactic’s definition accepts the Worker.Id
worth offered, passes that Id
to the SP, and retrieves the generated complicated 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 kind end result set, and our code is full. Returning to our instance question, we are able to use a easy command to return the division and different knowledge related to the primary worker in our database:
var staff = 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 strains of supporting code and yields a substantial payoff when utilizing EF Core to retrieve complicated outcomes.
The editorial workforce of the Toptal Engineering Weblog extends its gratitude to Alexander Skogorev for reviewing the technical content material and code samples introduced on this article.