Вместе с обычными 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, который отправляется из кода 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}"); }