Stored Procedure trong Entity Framework

Trung Nguyen
16/04/2020
10 min read

Nội dung chính của bài viết

Entity Framework có khả năng tự động xây dựng các lệnh truy vấn SQL cho cơ sở dữ liệu dựa trên các truy vấn LINQ-to-Entities hoặc Entity SQL của bạn, cũng như xây dựng các lệnh để thêm mới, cập nhật và xóa dữ liệu.

Bạn có thể muốn ghi đè các bước này bằng cách sử dụng các stored procedure đã được bạn định nghĩa trước.

Bạn có thể sử dụng các stored procedure để lấy dữ liệu hoặc thêm mới / cập nhật / xóa các bản ghi cho một hoặc nhiều bảng trong cơ sở dữ liệu.

Entity Framework API tạo một phương thức thay vì một thực thể trong EDM cho mỗi stored procedure và function do người dùng tự định nghĩa (UDF) trong cơ sở dữ liệu.

Chúng ta sẽ bắt đầu sử dụng stored procedure để lấy dữ liệu từ cơ sở dữ liệu.

Đầu tiên, tạo stored procedure tên là GetCoursesByStudentId trong cơ sở dữ liệu SQL Server của bạn. Stored procedure này trả về tất cả các khóa học của một sinh viên cụ thể.

CREATE PROCEDURE [dbo].[GetCoursesByStudentId]
    -- Add the parameters for the stored procedure here
    @StudentId int = null
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    select c.courseid, c.coursename, c.Location, c.TeacherId
    from student s 
        left join studentcourse sc on sc.studentid = s.studentid 
        left join course c on c.courseid = sc.courseid
    where s.studentid = @StudentId
END

Bây giờ, chúng ta sẽ thêm mô hình dữ liệu thực thể mới bằng cách nhấp chuột phải vào dự án trong Solution Explorer trong Visual Studio -> Add -> New Item. Điều này sẽ mở một cửa sổ bật lên Add New Item. Trong cửa sổ bật lên, chọn ADO.NET Entity Data Model, đặt tên cho EDM và nhấp vào nút Add. Điều này sẽ mở Entity Data Model Wizard. Chọn EF Designer from database và nhấp vào nút Next, như được trình bày ở bên dưới.

Tiếp theo, bạn cần tạo kết nối với cơ sở dữ liệu hiện tại của bạn. Nếu đây là lần đầu tiên bạn tạo EDM cho cơ sở dữ liệu của mình, thì bạn cần tạo kết nối mới bằng cách nhấp vào nút New Connection.... Ở đây, chúng tôi đã có kết nối, vì vậy chúng tôi sẽ chọn cơ sở dữ liệu từ danh sách thả xuống và nhấp vào nút Next.

Trong bước này, chọn stored procedure GetCoursesByStudentId bên dưới Stored Procedures and Functions. Đừng quên đánh dấu vào hộp kiểm Import selected stored procedures and functions into the entity model và sau đó nhấp vào Finish.

Bạn sẽ thấy stored procedure GetCoursesByStudentId được thêm vào trong Stored Procedures/Functions và Function Imports như hình bên dưới, với kiểu dữ liệu phức tạp là GetCoursesByStudentId_Result trong Model Browser.

Bất cứ khi nào thêm một stored procedure hoặc UDF vào EDM, nó sẽ tạo một kiểu dữ liệu phức tạp mới với tên là {sp name}_Result theo mặc định.

Stored procedure GetCoursesByStudentId trả vềcác trường được định nghĩa trong thực thể Course. Vì vậy, chúng ta không cần thêm một kiểu dữ liệu phức tạp mới cho GetCoursesByStudentId.

Bạn có thể thay đổi nó bằng cách nhấp chuột phải vào GetCoursesByStudentId trong Function Imports và chọn Edit. Điều này sẽ mở cửa sổ Edit Function Import như hình bên dưới.

Để thiết lập thực thể Course làm loại kết quả trả về cho stored procedure, chọn mục Entities và chọn Course từ danh sách thả xuống trong cửa sổ bật lên và nhấp vào OK, như được hiển thị bên dưới:

Điều này sẽ thêm phương thức có tên GetCoursesByStudentId trong lớp Context (kế thừa từ lớp DbContext) như dưới đây:

Bây giờ, bạn có thể sử dụng phương thức GetCoursesByStudentId để lấy dữ liệu như ví dụ bên dưới:

using (var context = new SchoolDBEntities())
{
    var courses = context.GetCoursesByStudentId(1);

    foreach (Course cs in courses)
    {
        Console.WriteLine(cs.CourseName);
    }
}

Ví dụ trên sẽ thực thi câu lệnh SQL sau trong cơ sở dữ liệu:

exec [dbo].[GetCoursesByStudentId] @StudentId=1

Thao tác CUD sử dụng stored procedure trong Entity Framework

Trong phần này, chúng ta sẽ sử dụng stored procedure cho các thao tác CUD (Create, Update, Delete) cho một thực thể khi chúng ta gọi phương thức SaveChanges() theo cách tiếp cận Database First.

Chúng tôi sẽ sử dụng các stored procedure sau đây cho thực thể Student:

sp_InsertStudent: Thêm một sinh viên mới vào cơ sở dữ liệu.
sp_UpdateStudent: Cập nhật một sinh viên trong cơ sở dữ liệu.
sp_DeleteStudent: Xóa một sinh viên trong cơ sở dữ liệu.

Dưới đây là các tập lệnh SQL cho mỗi thủ tục được lưu trữ.

Stored procedure sp_InsertStudent

CREATE PROCEDURE [dbo].[sp_InsertStudent]
    -- Add the parameters for the stored procedure here
    @StandardId int = null,
    @StudentName varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO [SchoolDB].[dbo].[Student]([StudentName],[StandardId])
    VALUES(@StudentName, @StandardId)

    SELECT SCOPE_IDENTITY() AS StudentId

END

Stored procedure sp_UpdateStudent

CREATE PROCEDURE [dbo].[sp_UpdateStudent]
    -- Add the parameters for the stored procedure here
    @StudentId int,
    @StandardId int = null,
    @StudentName varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    update [SchoolDB].[dbo].[Student] 
    set StudentName = @StudentName,
        StandardId = @StandardId
    where StudentID = @StudentId;

END

Stored procedure sp_DeleteStudent

CREATE PROCEDURE [dbo].[sp_DeleteStudent]
    -- Add the parameters for the stored procedure here
    @StudentId int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    delete [dbo].[Student]
    where StudentID = @StudentId

END

Trước tiên, bạn cần cập nhật EDM hiện tại của mình để thêm các stored procedure này vào EDM. Nhấp chuột phải vào trình thiết kế của bạn và nhấp vào Update Model from Database.. để mở trình hướng dẫn cập nhật.

Mở rộng nút Stored Procedures and Functions, chọn các stored procedure ở trên và bỏ chọn ở hộp kiểm Import selected stored procedures and function into the entity model vì chúng ta sẽ ánh xạ các stored procedure này với thực thể Student trực tiếp.

Thao tác CUD sử dụng stored procedure trong Entity Framework

Nhấp vào nút Finish. Trình duyệt mô hình sẽ hiển thị các stored procedure trong phần Storage model, nhưng không hiển thị trong Function Imports, như hình ảnh bên dưới.

Bạn cần ánh xạ các stored procedure này với thực thể Student. Trong trình thiết kế EDM, nhấp chuột phải vào thực thể Student và chọn Stored Procedure Mapping để mở chi tiết ánh xạ như hình ảnh bên dưới.

Trong chi tiết ánh xạ, như được hiển thị trong hình bên dưới, bạn sẽ thấy <Select Insert Function>, <Select Update Function> và <Select Delete Function>.

Chọn stored procedure thích hợp cho từng thủ tục trong danh sách thả xuống, ví dụ sp_InsertStudent cho chức năng thêm mới, sp_UpdateStudent cho chức năng cập nhật và sp_DeleteStudentcho chức năng xóa.

Stored procedure sp_InsertStudent trả về giá trị StudentId của sinh viên mới được thêm vào cơ sở dữ liệu và nó ánh xạ với thuộc tính StudentID của thực thể Student, như được hiển thị trong hình bên dưới:

Hoàn tất ánh xạ tham số của các stored procedure Chèn, Cập nhật và Xóa với các thuộc tính tương ứng của thực thể Student, như được hiển thị trong hình bên dưới.

Bây giờ, chúng ta cần xác thực nó trước khi thực hiện để đảm bảo rằng sẽ không có lỗi khi thực thi.

Để thực hiện điều này, nhấp chuột phải vào thực thể Student trong trình thiết kế và nhấp vào Validate và đảm bảo rằng không có cảnh báo hoặc lỗi nào.

Bây giờ, bất cứ khi nào bạn thêm, cập nhật hoặc xóa thực thể Student, Entity Framework (EF) sẽ sử dụng các stored procedure này cho các thao tác CUD, thay vì thực thi các lệnh SQL. Ví dụ sau đây minh chứng điều này:

using (var context = new SchoolDBEntities())
{
    Student student = new Student() { StudentName = "New student using SP" };

    context.Students.Add(student);
    //will execute sp_InsertStudent
    context.SaveChanges();

    student.StudentName = "Edit student using SP";
    //will execute sp_UpdateStudent
    context.SaveChanges();

    context.Students.Remove(student);
    //will execute sp_DeleteStudentInfo 
    context.SaveChanges();
}

Ví dụ trên sẽ thực hiện các câu lệnh sau trên mỗi khi gọi phương thức SaveChanges():

exec [dbo].[sp_InsertStudent] @StandardId=NULL,@StudentName='New student using SP'
go

exec [dbo].[sp_UpdateStudent] @StudentId=47,@StandardId=NULL,@StudentName='Edit student using SP'
go

exec [dbo].[sp_DeleteStudent] @StudentId=47
go

Lưu ý: Một khi nó thực thi phương thức SaveChanges sau khi thêm một sinh viên mới, nó sẽ gán một giá trị được tạo cho cơ sở dữ liệu cho thuộc tính StudentID.

Điều này là cần thiết để theo dõi nó và thực hiện các hành động tiếp theo trên đối tượng thực thể đó. Hình ảnh sau đây cho thấy giá trị trong chế độ gỡ lỗi của Visual Studio.

Table-Valued Function trong Entity Framework

Table-Valued Function (TVF) tương tự như stored procedure, ngoại trừ một điểm khác biệt chính: kết quả của table-valued function có thể được sử dụng trong truy vấn LINQ-to-Entity.

Sau đây là một table-valued function mẫu có tên GetCourseListByStudentID trong cơ sở dữ liệu SQL Server, sẽ trả về tất cả các khóa học của một sinh viên cụ thể.

USE [SchoolDB]
GO
/****** Object:  UserDefinedFunction [dbo].[GetCourseListByStudentID]  */  
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetCourseListByStudentID]
(    
    -- Add the parameters for the function here
    @studentID int
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    select c.courseid, c.coursename,c.Location, c.TeacherId
    from student s left outer join studentcourse sc on sc.studentid = s.studentid 
        left outer join course c on c.courseid = sc.courseid
    where s.studentid = @studentID
)

Bây giờ, bạn hãy thêm table-valued function này vào EDM bằng cách nhấp chuột phải vào trình thiết kế -> chọn Update Model from the Database.. như hình bên dưới.

Trong trình hướng dẫn cập nhật, mở rộng Stored Procedures and Functions -> Schema -> chọn GetCourseListByStudentID và nhấp vào Finish. Đừng quên đánh dấu vào hộp kiểm Import selected procedures and functions into the entity model.

Điều này sẽ thêm table-valued function như một chức năng và tạo ra một kiểu dữ liệu phức tạp GetCourseListByStudentID_Result cho kết quả.

Vì table-valued function GetCourseListByStudentID của chúng tôi trả về các cột từ bảng Course do đó chúng tôi có thể ánh xạ thực thể Course với kết quả thay vì sử dụng một kiểu dữ liệu phức tạp.

Để làm điều này bạn hãy mở trình duyệt mô hình -> Function Imports -> nhấp chuột phải vào 'GetCferenceListByStudentID' -> nhấp vào Edit:

Bạn sẽ thấy rằng EDM đã tự động tạo ra kiểu dữ liệu phức tạp GetCourseListByStudentID_Result như một kiểu danh sách trả về.

Thay đổi Returns a Collection Of thành Entities và chọn thực thể Course, như hình bên dưới

Bây giờ, bạn có thể thực thi một table-valued function như là một phương thức của DBContext, như được trình bày bên dưới:

using (var ctx = new SchoolDBEntities())
{
    //Execute TVF and filter result
    var courses = ctx.GetCourseListByStudentID(1)
                     .ToList<Course>();
}