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

Данное руководство устарело. Актуальное руководство: Руководство по Entity Framework Core 7

Наряду с выполнением обычных sql-запросов мы также можем выполнять хранимые функции и процедуры, которые имеются в базе данных. Рассмотрим вызов хранимой функции в приложении на C#.

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

public class Company
{
	public int Id { get; set; }
	public string Name { get; set; }

	public List<User> Users { get; set; } = new List<User>();
}

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<Company> Companies { get; set; }
	public DbSet<User> Users { get; set; }
        
	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#:

using(ApplicationContext db = new ApplicationContext())
{
    Microsoft.Data.SqlClient.SqlParameter param = new Microsoft.Data.SqlClient.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}");
}

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

Упрощение вызова в EntityFramework Core 5.0

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

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

public class ApplicationContext : DbContext
{
	public DbSet<Company> Companies { get; set; }
	public DbSet<User> Users { get; set; }
	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}");
}