سناریویی را در نظر بگیرید که قصد ندارید کوئری های ساده SQL توسط Entity اجرا شود . در این مقاله قصد داریم ترتیبی اتخاذ کنیم که Entity از Stored Procedure برای اجرای کوئری ها استفاده کند.

سناریویی را در نظر بگیرید که قصد ندارید   کوئری های ساده SQL توسط Entity اجرا شود .بلکه برای افزایش Performance می خواهید که این کوئری ها به صورت Stored Procedure توسط Entity اجرا شوند.

در این مقاله قصد داریم ترتیبی اتخاذ کنیم که Entity از Stored Procedure برای اجرای کوئری ها استفاده کند.در این پروژه به صورت Code First کار می کنیم .ابتدا یک کلاس  Product  به صورت زیر تعریف می کنیم .

class Product
{
  public int Id { get; set; }
  public string Name { get; set; }
  public decimal Price { get; set; }
}
 
class DataContext : DbContext
{
  public DbSet<Product> Products { get; set; }
}

بعد از افزودن این کلاس یک کنترلر به نام Product اضافه می کنیم .در پنجره ای که باز می شود نام مدل را Product تعیین می کنیم و با زدن +  از MVC می خواهیم که کلاس Context را هم برای ما ایجاد کند .کد کلاس Context به صورت زیر است .

در Action به نام Index  کد های زیر را می نویسیم

     // GET: Default
        private spContext db = new spContext();
        public ActionResult Index()
        {
            using (spContext db = new spContext())
            {
                Product product = new Product { Name = "Apple iPhone 6 Plus", Price = 1000.0m };
                db.Products.Add(product);
                db.SaveChanges();
                return View(product);
            }
        }

وقتی کد بالا را اجرا می کنید در SQL کوئری ساده زیر اجرا می شود .

exec sp_executesql N'INSERT [dbo].[Products]([Name], [Price])
VALUES (@0, @1)
SELECT [Id]
FROM [dbo].[Products]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()',N'@0 nvarchar(max) ,@1 decimal(18,2)',@0=N'Apple iPhone 6 Plus',@1=1000.00
go

برای اینکه از این کوئری های ساده فاصله بگیرید و Stored Procedure به Entity بدهید تا با سرعت بیشتری دستورات را اجرا کند دو راه حل پیش رو دارید

روش اول : ساخت Stored Procedure جدید

روش دوم : استفاده از Stored Procedure های موجود

روش اول : ساخت Stored Procedure جدید

Entity کلاس های مدل شما را که در DbContext آورده شده است می خواند و برای آنها metadata می سازد.در DbContext متدی به نام OnModelCreating وجود دارد که در این جا آن را بازنویسی (Override) می کنیم تا امکانات بیشتری به آن بیافزاییم. Entity از این امکانات و اطلاعات برای ساخت metadata برای کلاس های مدل استفاده می کند. با بازنویسی این متد به Entity می گوییم که از  Stored Procedure برای انجام عملیات بر روی دیتابیس استفاده کند.

در زیر کد مربوط به DbContext را می بینید

class DataContext : DbContext
{
   public DbSet<Product> Products { get; set; }
   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
     modelBuilder.Entity<Product>().MapToStoredProcedures();
     base.OnModelCreating(modelBuilder);
   }
}

بعد از انجام این تغییر در DbContext باید توسط Migration دیتابیس خود را به روز رسانی کنیم

برای انجام Migration مراحل زیر را دنبال کنید

در Tools ->Nuget Package Manager->Package Manager Consol  این مسیر وارد می شویم .کنسول در پایین باز خواهد شد.

دستور Enable-Migrations

Add-Migration

Update-DataBase

بعد از این کار به روزرسانی ها بین مدل و دیتابیس انجام می شود .یک کلاسی به همان نامی که Migration را Add کردید ایجاد می شود .در این کلاس می بینید که سه Stored procedure ایجاد شده است .اولی برای درج دومی به روزرسانی و سومی برای حذف رکورد ها می باشد. کد زیر Stored Procedure های نوشته شده را که طریقه نامگذاری آنها به صورت <Type>_<Action> می باشد نمایش می دهد.

 public partial class MappedSPtoProductEntity : DbMigration
{
  public override void Up()
  {
     CreateStoredProcedure(
       "dbo.Product_Insert",
       p => new
       {
         Name = p.String(),
         Price = p.Decimal(precision: 18, scale: 2),
       },
       body:
         @"INSERT [dbo].[Products]([Name], [Price])
            VALUES (@Name, @Price)
         DECLARE @Id int
         SELECT @Id = [Id]
         FROM [dbo].[Products]
         WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()
 
         SELECT t0.[Id]
         FROM [dbo].[Products] AS t0
         WHERE @@ROWCOUNT > 0 AND t0.[Id] = @Id"
     );
 
    CreateStoredProcedure(
      "dbo.Product_Update",
       p => new
       {
         Id = p.Int(),
         Name = p.String(),
         Price = p.Decimal(precision: 18, scale: 2),
       },
       body:
         @"UPDATE [dbo].[Products]
         SET [Name] = @Name, [Price] = @Price
         WHERE ([Id] = @Id)"
    );
 
    CreateStoredProcedure(
       "dbo.Product_Delete",
       p => new
       {
         Id = p.Int(),
       },
       body:
         @"DELETE [dbo].[Products]
         WHERE ([Id] = @Id)"
   );
}
 
public override void Down()
{
  DropStoredProcedure("dbo.Product_Delete");
  DropStoredProcedure("dbo.Product_Update");
  DropStoredProcedure("dbo.Product_Insert");
}
 
}

بعد از اینکه کد بالا را نوشتیم باید دیتابیس خود را به روزرسانی کنیم .دستور Update-Database را در کنسول مربوط به Nuget وارد کنید.وقتی این دستور را بنویسیدStored Procedure  هایی که در مدل نوشتید در سمت دیتابیس می بینید.به شکل زیر توجه کنید

حال با توجه به کد زیر می بینید که نحوه افزودن یک محصول تغییر کرده است

     // GET: Default
        private spContext db = new spContext();
        public ActionResult Index()
        {
            using (spContext db = new spContext())
            {
                Product product = new Product { Name = "Apple iPhone 6 Plus", Price = 1000.0m };
                db.Products.Add(product);
                db.SaveChanges();
                return View(product);
            }
        }

بعد از تغییرات بالا برای اجرا تنها دستور زیر توسط Entity اجرا می شود

exec [dbo].[Product_Insert] @Name=N'Google Nexus ',@Price=699.00

روش دوم : استفاده از Stored Procedure  های موجود

تا به اینجا نحوه ایجاد و اجرای Stored Procedure   برای مدل در حالت Code First را دیدیم .حال سناریوی دیگری را در نظر بگیرید که می خواهیم مدل خود را از روی دیتابیس موجود ایجاد کنیم .در این حالت بهتر است که از Stored Procedure   های موجود استفاده کنیم تا اینکه مانند قبل انها را دوباره بنویسیم.

در پروژه دیگری باز هم کلاس Product را به عنوان مدل ایجاد می کنیم

class Product
{
  public int Id { get; set; }
  public string Name { get; set; }
  public decimal Price { get; set; }
}

در کلاس spContext و در داخل سازنده یک کانکشن برای اتصال به دیتابیس موجود را نوشته ایم .

class DataContext : DbContext
{
  public DataContext()
  {
     Database.Connection.ConnectionString = "Data Source=.\\SQLExpress;Initial Catalog=CallingExistingSPFromEF6;Integrated Security=True";
  }
  public DbSet<Product> Products { get; set; }
}

پس از این باید متد OnModelCreating را بازنویسی کنیم .تا اطلاعات بیشتری به Entity برای ساخت متادیتای مربوط به این مدل بدهیم .

class DataContext : DbContext
{
  public DataContext()
  {
    Database.Connection.ConnectionString = "Data Source=.\\SQLExpress;Initial Catalog=CallingExistingSPFromEF6;Integrated Security=True";
  }
 
  public DbSet<Product> Products { get; set; }
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Product>().MapToStoredProcedures
    (
      s => s.Insert(i => i.HasName("[dbo].[Product_Insert_HandCoded]"))
            .Update(u => u.HasName("[dbo].[Product_Update_HandCoded]"))
            .Delete(d => d.HasName("[dbo].[Product_Delete_HandCoded]"))</pre>
<pre>    );
    base.OnModelCreating(modelBuilder);
  }
}

در کدهای بالا با دستور “[dbo].[Product_Insert_HandCoded]” دیتا در دیتابیس ذخیره خواهیم کرد.

حال با توجه به کد زیر یک محصول جدید را به دیتابیس اضافه می کنیم.

using (var db = new DataContext())
{
  Product product = new Product { Name = "MacBook Pro 13 Inch", Price = 1099.00m };
  db.Products.Add(product);
  db.SaveChanges();
}