آموزش کار با Stored Procedure در Entity Framework Core

آموزش کار با Stored Procedure در Entity Framework Core

در این بخش یاد می گیریم چگونه در EF Core با stored procedure کار کنیم.

EF Core متدهای زیر را برای اجرای stored procedure در اختیار می گذارد:

• DbSet.FromSql()
• DbSet.FromSql()

در EF Core2 محدودیت هایی در رابطه با اجرای stored procedure با استفاده از این دو متد وجود دارد:

• نتیجه باید از نوع یک entity باشد. این به این معناست که یک stored procedure باید حتما ستون هایی از جدول متناظر با یک entity برگرداند.
• نتیجه query نمی تواند حاوی Databaseی وابسته باشد. این به این معناست که stored procedure نمی تواند join بزند.
• عملیات های Insert، Update و Delete نمی تواند به بک entity نگاشت شود، در نتیجه متد SaveChanges نمی تواند stored procedure ها را برای عملیات CUD فراخوانی کند.

می خواهیم stored procedure را قبل از اجرا در EF Core، در MS SQL Server اجرا کنیم.

اگر رویکرد database-first را در پروژه دنبال می کنید، اسکریپت زیر را در Database SQL Server خود اجرا کنید:

USE [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

مراحل زیر را انجام دهید:

با اجرای دستور زیر در NuGet Package Manager یک migration خالی به پروژه اضافه کنید:

PM> Add-migration sp-GetStudents

کد زیر را در متد Up در migration ایجاد شده بنویسید:

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)
    {
    }
}

حال با اجرای دستور زیر sp را در Database بسازید:

PM> Update-database

این دستور یک stored procedure با نام GetStudents در Database SQL Server می سازد.

اجرای Stored Procedures با استفاده از FromSql

همانطور که در بخش قبل اشاره کردیم، متد FromSql روی DbSet می تواند برای اجرای SQL Query استفاده شود. به شیوه مشابه همین متد را می توان برای اجرای Stored Procedure استفاده کرد. اما این کار محدودیت هایی دارد.

در Database می توان GetStudents را با مقدار پارامتر INPUT اجرا کرد، مثلا:

GetStudents "Bill"
-- or
exec GetStudents "Bill"
در EF Core می توانید SP را با استفاده از متد FromSql به شیوه مشابه اجرا کنید:
var context = new SchoolContext(); 
var students = context.Students.FromSql("GetStudents 'Bill'").ToList

می توانید مقدار پارامتر را نیز به صورت زیر به آن پاس دهید:

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();

از SqlParameter برای تعیین مقدار پارامترهای IN و OUT استفاده کنید:

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();

می توانید برای پارامتر اول @p0 و برای پارامتر دوم @p1 تعیین کنید و به همین ترتیب.

var context = new SchoolContext(); 
var students = context.Students.FromSql("GetStudents @p0","Bill").ToList();

در مثال بالا @p0 برای اولین پارامتر استفاده شده چرا که پارامترهای نام گذاری شده در EF Core هنوز پشتیبانی نمی شوند.

توجه: به طور پیش فرض همه ی موجودیت های درون نتیجه ای که sp برمی گرداند توسط DbContext دنبال می شود. اگر یک sp را با پارامترهای مشابه چند بار اجرا کنید، دستور SQL یکسانی را هربار اجرا می کند، اما فقط یک مجموعه پاسخ را Track می کند. برای مثال، کد زیر سه بار GetStudents را اجرا می کند، اما فقط یک کپی از نتیجه را track می کند.

var context = new SchoolContext(); 
var list1 = context.Students.FromSql("GetStudents 'Bill'").ToList();
var list2 = context.Students.FromSql("GetStudents 'Bill'").ToList();
var list3 = context.Students.FromSql("GetStudents 'Bill'").ToList();

اجرای Stored Procedure با استفاده از ExecuteSqlCommand()

متد ExecuteSqlCommand() برای اجرای command های Database به شکل string استفاده می شود و یک عدد که نشانگر تعداد ردیف های تغییر کرده است، برمی گرداند.

var context = new SchoolContext(); 
var rowsAffected = context.Database.ExecuteSqlCommand("Update Students set FirstName = 'Bill' where StudentId = 1;")

در مثال بالا، update command به متد ExecuteSqlCommand پاس داده شده. مقدار rowsAffected یک است چرا که یک ردیف تحت اجرای این دستور تغییر کرده است.

به صورت مشابه، می توانیم Stored Procedure هایی برای create، update، و delete اجرا کنیم. Stored Procedure زیر را که یک رکورد در جدول Students درج می کند در نظر بگیرید:

 CREATE PROCEDURE CreateStudent
    @FirstName Varchar(50),
    @LastName Varchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    Insert into Students(
           [FirstName]
           ,[LastName]
           )
 Values (@FirstName, @LastName)
END
GO

حال می توان این Stored Procedure را به شکل زیر اجرا کرد:

var context = new SchoolContext(); 
context.Database.ExecuteSqlCommand("CreateStudents @p0, @p1", parameters: new[] { "Bill", "Gates" })