28.10.2024 15:59
SuleymaniTural 0 468 Источник

Введение

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

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

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

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

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

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

    Использование скалярных функций с EF Core

    В этом уроке мы продемонстрируем, как:

    1. Мигрировать пользовательскую скалярную функцию SQL в базу данных с использованием Entity Framework Core.

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

{
  "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<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-вызов функции при построении запросов.

  • .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<ActionResult<IEnumerable<SalesOrderDetail>>> 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).

Комментарии (0)