EF Core - Làm việc với Stored Procedure EF Core
Làm việc với Stored Procedure EF Core
Trong phần này, bạn sẽ học cách thực thi thủ tục lưu trữ (Stored Procedure ) cơ sở dữ liệu trong Entity Framework Core.
EF Core cung cấp các phương pháp sau để thực hiện một thủ tục được lưu trữ(Stored Procedure):
DbSet<TEntity>.FromSql()
DbContext.Database.ExecuteSqlCommand()
Có một số hạn chế về việc thực thi các thủ tục được lưu trữ trong cơ sở dữ liệu bằng phương pháp FromSql hoặc ExecuteSqlCommand trong EF Core2:
- Kết quả phải là một loại thực thể. Điều này có nghĩa là một thủ tục được lưu trữ phải trả về tất cả các cột của bảng tương ứng của một thực thể.
- Kết quả không thể chứa dữ liệu liên quan. Điều này có nghĩa là một thủ tục được lưu trữ không thể thực hiện các JOIN để hình thành kết quả.
- Các thủ tục Thêm, Cập nhật và Xóa không thể được ánh xạ với thực thể, vì vậy phương thức SaveChanges không thể gọi các thủ tục được lưu trữ cho các hoạt động CUD.
Chúng ta hãy tạo thủ tục được lưu trữ của chúng tôi trong MS SQL Server trước khi chúng tôi thực thi nó trong EF Core.
Nếu bạn làm theo cách tiếp cận cơ sở dữ liệu đầu tiên, thì hãy thực thi tập lệnh sau trong cơ sở dữ liệu SQL Server cục bộ của bạn:
SE [SchoolDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetStudents]
@FirstName varchar(50)
AS
BEGIN
SET NOCOUNT ON;
select * from Students where FirstName like @FirstName +'%'
END
GO
Nếu bạn đang làm theo cách tiếp cận mã đầu tiên, hãy làm theo các bước sau:
- Thêm migration bằng cách thực hiện lệnh sau trong NPM (NuGet Package Manager):
PM> Add-migration sp-GetStudents
- Viết mã sau trong phương thức Up của lớp migration trống trong
<DateTime>_sp-GetStudents.cs
:
public partial class spGetStudents : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
var sp = @"CREATE PROCEDURE [dbo].[GetStudents]
@FirstName varchar(50)
AS
BEGIN
SET NOCOUNT ON;
select * from Students where FirstName like @FirstName +'%'
END";
migrationBuilder.Sql(sp);
}
protected override void Down(MigrationBuilder migrationBuilder)
{
}
}
- Bây giờ, hãy tạo quy trình được lưu trữ ở trên trong cơ sở dữ liệu bằng cách thực hiện lệnh sau trong NPM:
PM> Update-database
Thao tác này sẽ tạo thủ tục được lưu trữ GetStudents trong cơ sở dữ liệu SQL Server.
Thực thi các thủ tục được lưu trữ bằng FromSql
Như đã đề cập trong phần trước, phương thức FromSql của DbSet có thể được sử dụng để thực thi các truy vấn SQL đến cơ sở dữ liệu bên dưới. Theo cách tương tự, nó có thể được sử dụng để thực hiện thủ tục được lưu trữ trả về dữ liệu thực thể, nhưng có một số hạn chế.
Trong cơ sở dữ liệu, chúng ta có thể thực thi thủ tục được lưu trữ GetStudents với giá trị tham số INPUT như bên dưới:
GetStudents "Bill"
-- or
exec GetStudents "Bill"
Chúng ta có thể thực thi SP bằng phương thức FromSql trong EF Core theo cách tương tự như trên, như hình dưới đây.
var context = new SchoolContext();
var students = context.Students.FromSql("GetStudents 'Bill'").ToList();
Chúng ta cũng có thể truyền một giá trị tham số bằng cú pháp nội suy chuỗi C #, như được hiển thị bên dưới.
var name = "Bill";
var context = new SchoolContext();
var students = context.Students
.FromSql($"GetStudents {name}")
.ToList();
//or
//var students = context.Students.FromSql($"exec GetStudents {name}").ToList();
Sử dụng đối tượng SqlParameter để chỉ định giá trị của các tham số IN hoặc OUT như bên dưới:
var context = new SchoolContext();
var param = new SqlParameter("@FirstName", "Bill");
//or
/*var param = new SqlParameter() {
ParameterName = "@FirstName",
SqlDbType = System.Data.SqlDbType.VarChar,
Direction = System.Data.ParameterDirection.Input,
Size = 50,
Value = "Bill"
};*/
var students = context.Students.FromSql("GetStudents @FirstName", param).ToList();
Chúng ta cũng có thể khai báo @ p0 cho tham số đầu tiên, @ p1 cho tham số thứ hai, v.v.
var context = new SchoolContext();
var students = context.Students.FromSql("GetStudents @p0","Bill").ToList();
Trong ví dụ trên, @ p0 được sử dụng cho tham số đầu tiên vì các tham số được đặt tên chưa được hỗ trợ trong EF Core.