使用标量函数实现 EF Core 的实用方法

一.介绍

在构建应用程序时，您可能使用标量函数在数据库端实现一些逻辑。在 SQL 中，标量函数是一种对单个值或少量输入值进行操作并始终返回单个值作为输出的函数。这些函数本质上是可重复使用的代码块，用于对数据执行计算或操作。

以下是标量函数的主要特征。

• 标量函数只有一个输出。无论标量函数有多少个输入，它始终都会产生一个输出值。
• 标量函数用于以各种方式转换或修改数据。这可能涉及计算、字符串操作、日期/时间操作等。
• 通过将复杂的逻辑封装在函数中，标量函数可以简化 SQL 查询并使其更具可读性和可维护性。
• SQL 附带一组用于常见操作的预定义标量函数。您还可以创建自己的自定义标量函数来满足特定需求。

本教程将演示如何将标量 SQL 函数迁移到数据库以及如何使用 Entity Framework Core（EF Core）调用它。

二.要求

在软件开发中，编码不是第一步。我们应该有一些要求，开发应该从分析这些要求开始。我们计划使用 SQL Server 2019的AdventureWorks2019 数据库，我们需要创建一个函数来计算由其 ID 标识的特定销售报价的总单价。我们将使用 Sales.SalesOrderDetail 表及其 UnitPrice 和 SalesOfferId 列。

三.入门

我更喜欢直接使用 SQL IDE（如 Microsoft SQL Server Management Studio）实现/编写 SQL 函数，然后将其复制到 Visual Studio 进行迁移。这是我们的函数。

CREATE OR ALTER FUNCTION [dbo].[ufn_GetTotalUnitPriceBySalesOfferId]
(
    @specialOfferId INT
)
RETURNS DECIMAL(16,2)
AS
BEGIN
    DECLARE @result DECIMAL(16,2);
    SELECT @result = SUM(UnitPrice)
    FROM Sales.SalesOrderDetail AS SOD
    WHERE SOD.SpecialOfferID = @specialOfferId;
    RETURN @result;
END

我们将与 EF Core 一起实现基本的 Asp.net Core Web API 项目。创建一个新的 Asp.net Core Web API 项目（在我们的存储库中称为 EfCoreWithScalarFunctionsAPI）。我们计划使用 EF Core，因此我们需要安装与 EF Core 相关的包。打开工具 -> nuget 包管理器 -> 包管理器控制台并输入以下命令。

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools

要从 Visual Studio 迁移 ufn_GetTotalUnitPriceBySalesOfferId，我们只需生成一个空的迁移文件。为此，只需键入 add-migration Initial 并按回车键。它应该会生成一个空的迁移文件。现在我们需要更新它。最后它应该是这样的。

using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace EfCoreWithScalarFunctionsAPI.Migrations
{
    /// <inheritdoc />
    public partial class Initial : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"
                CREATE FUNCTION ufn_GetTotalUnitPriceBySalesOfferId(@specialOfferId as int)
                RETURNS DECIMAL(16,2) AS
                BEGIN
                    DECLARE @result as decimal(16,2);
                    SELECT @result = SUM(Unitprice)
                    FROM Sales.SalesOrderDetail AS SOD
                    WHERE SOD.SpecialOfferID = @specialOfferId;
                    RETURN @result;
                END");
        }
        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"DROP FUNCTION dbo.ufn_GetTotalUnitPriceBySalesOfferId");
        }
    }
}

我们的 SQL 迁移文件已准备就绪，但我们没有任何引用数据库的连接字符串。

这是我们的 appsettings.json 文件。

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "AdventureWorksDb": "Data Source=.;Initial Catalog=AdventureWorks2019;Integrated Security=SSPI;TrustServerCertificate=TRUE;"
  },
  "AllowedHosts": "*"
}

在项目的根目录中添加一个名为 Database 的文件夹，并添加具有以下内容的 AdventureWorksDbContext。

using Microsoft.EntityFrameworkCore;
namespace EfCoreWithScalarFunctionsAPI.Database
{
    public class AdventureWorksDbContext : DbContext
    {
        public DbSet<SalesOrderDetail> SalesOrderDetails { get; set; }
        public decimal GetTotalUnitPriceBySpecialOfferId(int salesOfferId)
            => throw new System.NotImplementedException();
        public AdventureWorksDbContext(DbContextOptions<AdventureWorksDbContext> dbContextOptions)
            : base(dbContextOptions)
        { }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasDbFunction(typeof(AdventureWorksDbContext)
                .GetMethod(nameof(GetTotalUnitPriceBySpecialOfferId), new[] { typeof(int) }))
                .HasName("ufn_GetTotalUnitPriceBySalesOfferId");
            base.OnModelCreating(modelBuilder);
        }
    }
}

我们的 AdventureWorksDbContext 指定了一个名为 GetTotalUnitPriceBySpecialOfferId 的方法。它没有任何实现，因为在运行时它将映射到我们的标量函数。为了正确地与我们的函数交互，我们应该重写 OnModelCreating 方法并在那里构造我们的函数。在模型配置期间，此方法被称为 Entity Framework Core (EF Core)，以允许您定义 C# 类如何映射到数据库架构。

四.在 OnModelCreating 中

1. **modelBuilder.HasDbFunction(…)：**此行配置数据库函数（UDF - 用户定义函数），EF Core 可以在构建查询时将其转换为等效的 SQL 函数调用。第一个参数（type of(AdventureWorksDbContext)）指定包含 UDF 方法（GetTotalUnitPriceBySpecialOfferId）的类。
2. .GetMethod(name of (GetTotalUnitPriceBySpecialOfferId), new[]{type (int) }):检索该类中特定方法的反射信息。
3. **（GetTotalUnitPriceBySpecialOfferId）的名称：**以字符串形式获取方法的名称。
4. **new[] {type (int) }：**创建一个数组，指定 UDF 采用 int（整数）参数。
5. **.HasName(“ufn_GetTotalUnitPriceBySalesOfferId”)：**这将配置 EF Core 在生成的 SQL 查询中将用于 UDF 的名称。此处，它设置为“ufn_GetTotalUnitPriceBySalesOfferId”（假设这是数据库中 UDF 的实际名称）。
6. **base.OnModelCreating(modelBuilder);：**这将调用基类的 OnModelCreating 实现，它可能包含特定于您的应用程序的附加模型配置。

最后，此代码告诉 EF Core 将 AdventureWorksDbContext 类中的自定义方法 (GetTotalUnitPriceBySpecialOfferId) 识别为数据库函数。当我们在 LINQ 查询中使用此方法时，EF Core 将使用提供的名称 (“ufn_GetTotalUnitPriceBySalesOfferId”) 将其转换为等效的 SQL 调用。这允许您直接在数据库查询中利用 C# 逻辑。

唯一缺少的项目是我们的 SalesOrderDetail 模型。

using System.ComponentModel.DataAnnotations.Schema;
namespace EfCoreWithScalarFunctionsAPI.Database
{
    [Table("SalesOrderDetail", Schema = "Sales")]
    public class SalesOrderDetail
    {
        public int SalesOrderDetailId { get; set; }
        public int SalesOrderId { get; set; }
        public int? ProductId { get; set; }
        public decimal UnitPrice { get; set; }
        public decimal UnitPriceDiscount { get; set; }
        public decimal LineTotal { get; set; }
        public int SpecialOfferId { get; set; }
    }
}

最后，我们需要更新我们的 Program.cs 来识别我们的数据库连接。

现在从 Nuget 包管理器控制台运行 update-database 命令，它应该将我们的标量函数迁移到 AdventureWorks2019 数据库。

五.使用标量函数

为了调用我们新创建的函数，让我们创建一个新的控制器（AdventureWorksController），内容如下。

using EfCoreWithScalarFunctionsAPI.Database;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Infrastructure;
using Microsoft.EntityFrameworkCore;
namespace EfCoreWithScalarFunctionsAPI.Controllers
{
    [ApiController]
    [Route("api/[controller]")]
    public class AdventureWorksController : ControllerBase
    {
        private readonly AdventureWorksDbContext _adventureWorksDbContext;
        public AdventureWorksController(AdventureWorksDbContext adventureWorksDbContext)
        {
            _adventureWorksDbContext = adventureWorksDbContext;
        }
        [HttpGet]
        public async Task<ActionResult<IEnumerable<SalesOrderDetail>>> GetSalesOrderInformationAsync()
        {
            var response = await (from sod in _adventureWorksDbContext.SalesOrderDetails
                                  where _adventureWorksDbContext.GetTotalUnitPriceBySpecialOfferId(sod.SpecialOfferId) > 10_000
                                  select sod)
                                  .Take(10)
                                  .ToListAsync();
            return Ok(response);
        }
    }
}

此代码定义了一个名为 AdventureWorksController 的控制器，用于处理与销售订单详细信息相关的 HTTP 请求。它通过构造函数注入 AdventureWorksDbContext 实例以与数据库交互。

GetSalesOrderInformationAsync 方法是检索销售订单详细信息的异步​​操作。它使用 LINQ 查询 SalesOrderDetails 表。

查询筛选详细信息，其中自定义函数 GetTotalUnitPriceBySpecialOfferId 返回关联的 SpecialOfferId 的总单价超过 10,000。然后，它使用 Take(10) 将结果限制为前 10 名。最后，将检索到的详细信息异步转换为列表，并使用 Ok(response) 返回成功的 HTTP 响应（状态代码 200）。