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