Данное руководство устарело. Актуальное руководство: Руководство по 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 была добавлена возможность создания в классе контекста метода, который проецируется на хранимую функцию и через который можно вызывать данную функцию.
Например, выше была определена хранимая табличная функция 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}"); }