При разработке приложения некоторая логика может быть реализована на стороне базы данных с использованием скалярных функций. В 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, что позволяет вам расширить функциональность ваших запросов и использовать преимущества существующей логики базы данных в вашем коде.
ссылка на оригинал статьи https://habr.com/ru/articles/849256/
Добавить комментарий