[Перевод] Практический подход к EF Core с использованием скалярных функций
Введение
При создании приложения некоторые логические операции могут быть реализованы на стороне базы данных с использованием скалярных функций. В SQL скалярная функция — это тип функции, который работает с одним значением или небольшим количеством входных значений и всегда возвращает одно значение в качестве результата. Эти функции представляют собой переиспользуемые блоки кода, которые выполняют вычисления или манипуляции с данными.
Основные характеристики скалярных функций
Единичный результат: Независимо от того, сколько входных значений принимает скалярная функция, она всегда производит одно выходное значение.
Трансформация данных: Скалярные функции часто используются для преобразования или модификации данных. Это может включать вычисления, работу со строками, операции с датой и временем и многое другое.
Инкапсуляция логики: Инкапсулируя сложную логику внутри функции, скалярные функции могут упростить SQL-запросы, делая их более читаемыми и поддерживаемыми.
Предопределенные и пользовательские функции: SQL предоставляет набор предопределенных скалярных функций для выполнения часто используемых операций, таких как математические и строковые функции. Также можно создать собственные скалярные функции для решения специфических задач.
Использование скалярных функций с EF Core
В этом уроке мы продемонстрируем, как:
Мигрировать пользовательскую скалярную функцию SQL в базу данных с использованием Entity Framework Core.
Вызвать и использовать эту функцию в вашем .NET приложении, обеспечив плавную интеграцию между логикой базы данных и кодом приложения.
Мы будем использовать базу данных AdventureWorks2019 для наших примеров. Вы можете скачать базу данных AdventureWorks2019 по этой ссылке.
Требования
В разработке программного обеспечения кодирование — это не первый шаг. Мы должны иметь некоторые требования, и разработка должна начинаться с их анализа. Мы планируем использовать базу данных AdventureWorks2019 и должны создать функцию, которая будет вычислять общую цену единицы для конкретного предложения, определенного по его ID. Мы будем использовать таблицу
Sales.SalesOrderDetail
и ее столбцыUnitPrice
иSalesOfferId
.
Начало работы
Если вам не нравится читать статьи, вот моё видео на YouTube, где я объясняю всё по шагам.
Я предпочитаю реализовывать/писать SQL-функции напрямую с использованием SQL IDE, таких как 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
Мы реализуем базовый проект веб-API на Asp.net Core вместе с EF Core. Создайте новый проект Asp.net Core Web API (в нашем репозитории он называется EfCoreWithScalarFunctionsAPI
). Мы планируем работать с EF Core, поэтому нам нужно установить пакеты, связанные с EF Core. Откройте Tools → NuGet Package Manager → Package Manager Console и введите следующие команды.
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools
Для миграции 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 файл миграции готов, но у нас нет строки подключения, которая бы ссылалась на нашу базу данных.
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"ConnectionStrings": {
"AdventureWorksDb": "Data Source=.;Initial Catalog=AdventureWorks2019;Integrated Security=SSPI;TrustServerCertificate=TRUE;"
},
"AllowedHosts": "*"
}
Конфигурация файла appsettings.json
Добавьте папку 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-вызов функции при построении запросов..GetMethod(name of (GetTotalUnitPriceBySpecialOfferId), new[] {type (int) })
: получает информацию о методе через рефлексию.name of (GetTotalUnitPriceBySpecialOfferId)
: получает имя метода в виде строки.new[] {type (int) }
: создает массив, указывающий, что UDF принимает параметр типа int..HasName("ufn_GetTotalUnitPriceBySalesOfferId")
: настраивает имя, которое EF Core будет использовать для UDF в сгенерированных SQL-запросах.base.OnModelCreating(modelBuilder);
: вызывает реализацию методаOnModelCreating
базового класса.
В конечном итоге этот код сообщает EF Core распознать пользовательский метод (GetTotalUnitPriceBySpecialOfferId
) в вашем классе AdventureWorksDbContext
как функцию базы данных. Когда мы используем этот метод в наших LINQ-запросах, EF Core переведет его в эквивалентный SQL-вызов с использованием предоставленного имени (»ufn_GetTotalUnitPriceBySalesOfferId
»). Это позволяет вам использовать C# логику напрямую в SQL-запросах.
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 Package Manager, и это должно мигрировать нашу скалярную функцию в базу данных 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()
{
//this is just an example of using it, not an optimal solution...
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)
.
Habrahabr.ru прочитано 12695 раз