.NET developers often need to call a database stored procedure (SP) from their C# server layer. Microsoft’s 实体框架(EF)核心 可用于将sp映射或导入为函数，但是, unfortunately, EF Core doesn’t natively support the retrieval of complex results from stored procedures. This is due to limitations in EF Core’s out-of-the-box solution that:

• 将存储过程的结果限制为 Entity type.
• 不能返回复杂类型以响应 JOIN command.
• 使创建、更新和删除操作不可用.

我们可以使用 C#, .NET, Microsoft SQL Server和EF Core合作. 此解决方案可用于任何 .. net支持的数据库或 .NET language that supports EF Core, provided the utility code is translated into that language. We’ll look at an example stored procedure to see how a few simple adjustments can overcome EF Core’s constraints.

具有复杂结果的假设存储过程

Let's consider GetEmployeesWithDepartment, a stored procedure that returns a complex result containing information from two related database tables, Employee and Department:

The Employee 表通过 foreign key from its ManagerId field. 它还引用了 Department table from the Employee.DepartmentId 字段连接到 Department table’s Id column. 这些表之间的序数关系是:

Relationships = Employee(1) : Department(1) and Department(1) : Employees(N)

Now let’s look at GetEmployeesWithDepartment，一个返回an的SP Employee 与输入参数匹配的表行 Employee.Id. Our SP returns the Id value and all of its associated information, such as the employee’s Department and Name values:

创建或修改过程[dbo].(GetEmployeesWithDepartment) 	
    @id INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT [E].*, [D].[姓名]AS[部门]
    FROM [dbo].[Employee] [E]
        INNER JOIN [dbo].[部门][D] ON [E].[departmentd] = [D].[Id]
    WHERE [E].[Id] >= @id
END

Let’s say we want to determine the department associated with the first employee listed in a 简单测试数据库 (in our example, the first employee listed is John in Engineering). We would like to execute this SP from our C# code, so let’s configure EF Core to support calling GetEmployeesWithDepartment 作为参数化SP.

注意:在继续之前， 构建数据库 using the Scaffold-DbContext 命令，或使用 创建上下文脚手架 command in .NET Core CLI.

步骤1:创建存储过程结果集模型

首先，我们将创建一个名为 GetEmployeesWithDepartment_Result.cs 然后定义复杂返回类型的结构:

公共类GetEmployeesWithDepartment_Result
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int DepartmentId { get; set; }
    public int? ManagerId { get; set; }
    public int Salary { get; set; }
    public decimal? Bonus { get; set; }
    public string Department { get; set; }
}

使用Microsoft SQL Server as the database server, we can explicitly verify the SP result column types by executing the sp_describe_first_result_set command:

EXEC sp_describe_first_result_set N'[dbo].(GetEmployeesWithDepartment)”

该命令显示 存储过程的列 以及相关的类型列表. With the result type defined, we move on to updating our EF model.

步骤2:将模型包含在 DbContext File

We are ready to incorporate the result model into our application’s EF Core DbContext file. EF provides an elegant approach to extending an application’s data model. Such 支持扩展 使用分部类，特别是使用 OnModelCreatingPartial method. To keep EF Core’s scaffolding tools from modifying our custom code, we’ll add our result model to EFCoreSPContext.SP.cs，一个局部c#类:

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

namespace EFCoreSP.Data
{
    公共部分类EFCoreSPContext: DbContext
    {
        public virtual DbSet
            GetEmployeesWithDepartment_Results { get; set; }

        //我们将在这里添加后续更改
    }
}

Here’s how EFCoreSPContext.SP.cs looks in our repository. We now need to add code that identifies our model’s primary key, if one is present.

步骤3:指定模型的密钥

We’ll indicate whether our SP’s result set has a key value by configuring our model in an OnModelCreatingPartial method in our EFCoreSPContext definition.

如果结果集有键值，则使用 HasKey method to explicitly identify the property associated with that key value:

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

如果实体没有键值，则使用 HasNoKey method instead:

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

我们的模型定义现在已经完成了. We’re ready to call the SP and retrieve our example employee data.

调用复杂的存储过程:简单如1-2-3

To simplify calling our SP, we’ll add one more public method to the EFCoreSPContext file. 方法的定义接受 Employee.Id 所提供的价值传递了它 Id to the SP, and retrieves the generated complex results as a list:

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

Our DbContext file is now ready to call a stored procedure and return a complex type result set, and our code is complete. 返回到我们的示例查询, we can use a simple command to return the department and other data associated with the first employee in our database:

var employees = dbContext.SP_GetEmployeesWithDepartment (1);

我们应用了一个简单的, 然而聪明和强大, solution to return a non-database entity from a stored procedure. This approach entails relatively few lines of supporting code and yields a considerable payoff when using EF Core to retrieve complex results.

The editorial team of the Toptal Engineering Blog extends its gratitude to Alexander Skogorev for reviewing the technical content and code samples presented in this article.