Хранимые функции

Вместе с обычными sql-запросами Entity Framework Core также позволяет выполнять хранимые функции, которые созданы в базе данных. Рассмотрим вызов хранимой функции в приложении на C# на примере БД MS SQL Server.

Пусть наша база данных описывается следующим контекстом данных и сущностями:

using Microsoft.EntityFrameworkCore;

public class Company
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public List<User> Users { get; set; } = new();
}

public class User
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public int Age { get; set; }
    public int CompanyId { get; set; }
    public Company? Company { get; set; }
}
public class ApplicationContext : DbContext
{
    public DbSet<User> Users { get; set; } = null!; 
    public DbSet<Company> Companies { get; set; } = null!;

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=helloappdb;Trusted_Connection=True;");
    }
}

Вначале создадим саму хранимую функцию, к которой мы будем обращаться. Для этого в Visual Studio в окне SQL Server Object Explorer откроем узел базы данных. В узле базы данных откроем подузел Programmability -> Functions -> Table-valued Functions:

Далее нажмем на этот узел правой кнопкой мыши и выберем Add New -> Table-valued Function:

После этого Visual Studio генерирует и открывает файл скрипта со следующим содержимым:

CREATE FUNCTION [dbo].[Function]
(
	@param1 int,
	@param2 char(5)
)
RETURNS @returntable TABLE
(
	c1 int,
	c2 char(5)
)
AS
BEGIN
	INSERT @returntable
	SELECT @param1, @param2
	RETURN
END

Изменим скрипт следующим образом:

CREATE FUNCTION [dbo].[GetUsersByAge]
(
    @age int
)
RETURNS @returntable TABLE
(
    Id int,
    CompanyId int,
    Name nvarchar(50),
    Age int
)
AS
BEGIN
    INSERT @returntable
    SELECT Id, CompanyId, Name, Age FROM Users WHERE Age < @age
    RETURN
END

Выражение CREATE FUNCTION [dbo].[GetUsersByAge] создае новую функцию GetUsersByAge. После ее названия определяется список параметров. Пусть наша функция принимает только один параметр @age, который имеет тип int, то есть целочисленное значение.

Затем идет определение возвращаемого объекта-таблицы в выражении RETURNS @returntable TABLE(...). В скобках идет перечисление столбцов возвращаемой таблицы. В данном случае они совпадают с определением таблицы Users. То есть таблица будет содержать объекты класса User.

Между выражениями BEGIN и END идет собственно выполнение функции. В данном случае с помощью оператора WHERE функция будет находить все строки, у которых столбец Age содержит меньшее значение, чем в параметре @age.

Теперь добавим функцию в базу данных. Для этого нажмем на кнопку Update:

Затем в появившемся окне нажмем на кнопку Update Database

После этого в базу данных будет добавлена функция, и ее можно будет использовать. Но перед обращением к ней из кода c# мы ее протестируем, чтобы убедиться, что она работает как надо. Для этого нажмем на функцию правой кнопкой мыши и в появившемся меню выберем пункт Execute Function:

После этого откроется окно для установки входных параметров функции. Введем в поле Value какое-нибудь число, которое будет передаваться в функцию в качестве параметра:

И Visual Studio сгенерирует и сразу же выполнит скрипт с функцией и переданным в нее параметром:

Как видно, я получил ожидаемые результаты, значит, функция работает правильно. Теперь обратимся к функции в коде на C#. Для этого мы можем воспользоватся одним из двух подходов.

Обращение к функции в запросе SQL

Первый подход предполагает обращение к хранимой функции в запросе SQL, который отправляется из кода C#:

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

using (ApplicationContext db = new ApplicationContext())
{
    SqlParameter param = new SqlParameter("@age", 30);
    var users = db.Users.FromSqlRaw("SELECT * FROM GetUsersByAge (@age)", param).ToList();
    foreach (var u in users)
        Console.WriteLine($"{u.Name} - {u.Age}");
}

В данном случае в запросе вместо таблицы указываем имя вызов функции с переданными ей параметрами: GetUsersByAge (@age)

В итоге результат данного запроса будет таким же, что и при выполнении скрипта выше.

Проецирование хранимой функции на метод класса

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

Например, выше была определена хранимая табличная функция GetUsersByAge, которая в качестве параметра принимает некоторое число - возраст и возвращает набор пользователей (по сути набор объектов User). Создадим для этой функции метод. Для этого изменим класс контекста следующим образом:

using Microsoft.EntityFrameworkCore;

public class ApplicationContext : DbContext
{
	public DbSet<Company> Companies { get; set; } = null!;
	public DbSet<User> Users { get; set; } = null!;
	public IQueryable<User> GetUsersByAge(int age)  => FromExpression(() => GetUsersByAge(age));
	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=helloappdb;Trusted_Connection=True;");
	}
	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder.HasDbFunction(() => GetUsersByAge(default));
	}
}

Здесь добавлен метод GetUsersByAge(), который соответствует хранимой функции в БД. Он принимает параметр типа int и возвращает объект IQueryable<User>. Этот метод с помощью встроенного в классе DbContext метода FromExpression вызывает GetUsersByAge(age).

public IQueryable<User> GetUsersByAge(int age)  => FromExpression(() => GetUsersByAge(age));

Далее в переопрепределенном методе OnModelCreating() класса контекста нам надо зарегистрировать метод GetUsersByAge с помощью вызова HasDbFunction():

modelBuilder.HasDbFunction(() => GetUsersByAge(default));

Далее мы можем обратиться к хранимой функции GetUsersByAge в коде следующим образом:

using (ApplicationContext db = new ApplicationContext())
{
	var users = db.GetUsersByAge(30);	// обращение к хранимой функции
	foreach (var u in users)
		Console.WriteLine($"{u.Name} - {u.Age}");
}