[Перевод] Практический подход к EF Core с использованием скалярных функций09.10.2024 10:46

При разработке приложения некоторая логика может быть реализована на стороне базы данных с использованием скалярных функций. В SQL скалярная функция — это тип функции, которая работает с одним или небольшим количеством входных значений и всегда возвращает одно значение в качестве результата. Эти функции представляют собой переиспользуемые блоки кода, которые выполняют вычисления или манипуляции с данными.

Вот основные характеристики скалярных функций:

  • Единичный результат: Скалярная функция всегда возвращает одно значение, независимо от того, сколько входных данных она принимает.

  • Модификация данных: Скалярные функции используются для трансформации или изменения данных различными способами. Это может включать вычисления, манипуляции со строками, операции с датами/временем и многое другое.

  • Упрощение запросов: Инкапсуляция сложной логики внутри функции позволяет упростить SQL-запросы, делая их более читаемыми и поддерживаемыми.

  • Предопределённые и пользовательские функции: В SQL существует набор предопределённых скалярных функций для выполнения распространённых операций. Также можно создавать собственные пользовательские скалярные функции для решения конкретных задач.

В этом руководстве будет показано, как мигрировать вашу скалярную SQL-функцию в базу данных и как вызывать её с помощью Entity Framework Core (EF Core).

Вы можете скачать базу данных Adventureworks2019 отсюда.

Требования

В разработке программного обеспечения кодирование не является первым шагом. Сначала необходимо иметь требования, и разработка должна начинаться с их анализа. Мы планируем использовать базу данных AdventureWorks2019, и нам нужно создать функцию, которая вычисляет общую стоимость за единицу товара для конкретного предложения продажи, идентифицированного его ID. Мы будем использовать таблицу Sales.SalesOrderDetail и её столбцы UnitPrice и SalesOfferId.

salesorderdetail table

salesorderdetail table

Я предпочитаю реализовывать и писать SQL-функции напрямую с использованием IDE для SQL, такой как Microsoft SQL Server Management Studio, а затем копировать их в 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

Мы будем реализовывать базовый проект Web API на ASP.NET Core вместе с EF Core. Создайте новый проект Web API на ASP.NET Core (в нашем репозитории он называется EfCoreWithScalarFunctionsAPI). Так как мы планируем работать с EF Core, нам нужно установить пакеты, связанные с EF Core. Откройте Инструменты → Менеджер пакетов NuGet → Консоль диспетчера пакетов и введите следующие команды. 

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

О, я забыл упомянуть: если чтение кажется скучным, вот версия видео на YouTube, где я всё объясняю с нуля и более подробно.


Чтобы мигрировать функцию ufn_GetTotalUnitPriceBySalesOfferId из Visual Studio, нам нужно просто сгенерировать пустой файл миграции. Для этого введите команду add-migration Initial и нажмите Enter. Это должно сгенерировать пустой файл миграции. Теперь нам нужно его обновить. Вот как он должен выглядеть в конечном итоге. 

using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace EfCoreWithScalarFunctionsAPI.Migrations
{
    /// 
    public partial class Initial : Migration
    {
        /// 
        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");
        }
        /// 
        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 SalesOrderDetails { get; set; }
        public decimal GetTotalUnitPriceBySpecialOfferId(int salesOfferId)
            => throw new System.NotImplementedException();
        public AdventureWorksDbContext(DbContextOptions 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:

  • modelBuilder.HasDbFunction (…): Эта строка конфигурирует функцию базы данных (UDF — определяемая пользователем функция), которую EF Core может преобразовать в эквивалентный SQL-вызов функции при построении ваших запросов. Первый аргумент (type of (AdventureWorksDbContext) указывает класс, содержащий метод UDF (GetTotalUnitPriceBySpecialOfferId).

  • .GetMethod (name of (GetTotalUnitPriceBySpecialOfferId), new[] {type (int) }): Получает информацию о методе через рефлексию для конкретного метода в этом классе.

  • name of (GetTotalUnitPriceBySpecialOfferId): Получает имя метода в виде строки.

  • new[] {type (int) }: Создаёт массив, указывающий, что функция UDF принимает параметр типа int (целое число).

  • .HasName («ufn_GetTotalUnitPriceBySalesOfferId»): Настраивает имя, которое EF Core будет использовать для UDF в сгенерированных SQL-запросах. Здесь оно установлено как «ufn_GetTotalUnitPriceBySalesOfferId» (предполагая, что это фактическое имя UDF в вашей базе данных).

  • base.OnModelCreating (modelBuilder);: Вызывает реализацию OnModelCreating базового класса, которая может содержать дополнительные конфигурации моделей, специфичные для вашего приложения.

В конечном итоге этот код сообщает EF Core, что он должен распознавать пользовательский метод (GetTotalUnitPriceBySpecialOfferId) в вашем классе AdventureWorksDbContext как функцию базы данных. Когда мы будем использовать этот метод в наших LINQ-запросах, EF Core преобразует его в эквивалентный SQL-вызов, используя предоставленное имя («ufn_GetTotalUnitPriceBySalesOfferId»). Это позволяет вам использовать логику 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, чтобы указать строку подключения к базе данных.

Теперь выполните команду update-database в консоли диспетчера пакетов NuGet, и это должно мигрировать нашу скалярную функцию в базу данных 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>> 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 возвращает общую стоимость единицы товара, превышающую 10 000, для связанного SpecialOfferId. Затем результаты ограничиваются первыми 10 записями с помощью Take(10). В конечном итоге извлечённые детали асинхронно конвертируются в список и возвращаются как успешный HTTP-ответ (код состояния 200) с использованием Ok(response).

Заключение

В данном руководстве мы прошли через ключевые шаги для интеграции скалярных функций SQL с использованием EF Core в приложении на ASP.NET Core. Мы начали с определения требований, где использовали базу данных AdventureWorks2019 для создания функции, вычисляющей общую стоимость единицы товара по ID предложения продажи.

Мы изучили, как мигрировать SQL-функцию в базу данных с помощью EF Core. Процесс включал создание пустого файла миграции и его обновление для включения нашей скалярной функции. Мы также добавили строку подключения к базе данных в appsettings.json и настроили контекст базы данных AdventureWorksDbContext, используя метод OnModelCreating для маппинга нашей функции.

После этого мы рассмотрели, как вызывать нашу функцию в контроллере AdventureWorksController, используя LINQ-запросы. Мы использовали метод GetTotalUnitPriceBySpecialOfferId, который был сопоставлен с нашей SQL-функцией, и показали, как вернуть результат в HTTP-ответе.

Таким образом, мы продемонстрировали, как интегрировать пользовательские SQL-функции в C# приложение с использованием EF Core, что позволяет вам расширить функциональность ваших запросов и использовать преимущества существующей логики базы данных в вашем коде.

© Habrahabr.ru