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

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

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

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

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

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

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

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

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

Требования

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

Я предпочитаю реализовывать и писать 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 {     /// <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:

• 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<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 возвращает общую стоимость единицы товара, превышающую 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, что позволяет вам расширить функциональность ваших запросов и использовать преимущества существующей логики базы данных в вашем коде.