Calling Stored Procedure from Entity Framework 6 Code First

You don’t want Entity Framework to execute plain SQL queries when inserting/updating data into database when using Code First.

Instead, you would like to call Stored Procedures.

Let us see first what the existing behavior is when saving information to the database and how we can make Entity Framework to use Stored Procedures instead of SQL queries.

Below is the simple Model (Product) class and the DbContext class associated

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; }
}

In the below code, I’ve added Product information and calling SaveChanges to save it to the database.

using(DataContext db = new DataContext())
{
  Product product = new Product { Name = "Apple iPhone 6 Plus", Price = 1000.0m };
  db.Products.Add(product);
  db.SaveChanges();
}

When you run the above code, following is the SQL query executed.

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

As you can see, this is just a plain SQL query. You might want to use Stored Procedures for better performance.

There are 2 options when you decide to use Stored Procedure

1. Creating a new Stored Procedure
2. Using existing Stored Procedure

Creating a new Stored Procedure:

Entity Framework reads your model classes which you have exposed in your DbContext class using DbSet (Product class in our case) and builds metadata about your model. OnModelCreating is the method in DbContext class which you can override to provide more information about your model. Entity Framework can make use of this information to create the metadata for your model.

I am going to override OnModelCreating method telling Entity Framework to use Stored Procedures. In the below line of code, I am hinting Entity Framework to use Stored Procedures for Product Entity.

modelBuilder.Entity<Product>().MapToStoredProcedures();

Below is the complete DbContext code.

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

After making the above changes, I ran the below “Add-Migration” command

Add-Migration “Mapped SP to Product Entity”

This would generate following DB Migration class for your product entity. You can notice that it generates 3 stored procedures – first for inserting Product, second for updating Product and last one for deleting the product. The naming convention of the stored procedure is <Type>_<Action> – For Inserting a product, the name of SP is Product_Insert.

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");
}

}

As we have changed the database model by creating new stored procedures for Product entity, we should update the database by running “Update-Database” command in Package manager Console. When you do so, all the 3 stored procedures would be created in the database.

Now, let us run our application again with only the Product value changed – we are trying to insert a new Product ‘Google Nexus 6’ and see how Entity Framework handles it.

using(DataContext db = new DataContext())
{
  Product product = new Product { Name = "Google Nexus ", Price = 699.0m };
  db.Products.Add(product);
  db.SaveChanges();
}

It will execute only the following stored procedure – not insert sql query.

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

Using existing Stored Procedures:

We saw how we can create and execute the stored procedures for our model in Entity Framework Code First approach. If we are using existing database with Stored Procedures, it may be preferable to use the existing stored procedures instead of creating new stored procedures.

I have created a new Console application – which same Product model.

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

I have added constructor for DataContext – to set the connection string for existing database which Entity Framework can make use of.

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

We need to override the OnModelCreating method to tell Entity Framework to tell more information about our model classes – as it would use this information when creating metadata for the model. When you call MapToStoredProcedures for an entity without any parameters, Entity Framework would assume that we are asking it to create new stored procedures for that entity. You can pass parameters to this method to tell Entity Framework to use the existing stored procedures.

In the below code, I have asked to use “[dbo].[Product_Insert_HandCoded]” SP for inserting data into the database. The lambda s value in MapToStoredProcedures parameter is ModificationStoredProcedureConfiguration.

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]"))
  );

  base.OnModelCreating(modelBuilder);
}

The complete code is attached below

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

In Main method of Console application, I am adding Product information and saving it to the database.

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

Now, it will use your handcoded stored procedures for inserting/updating/deleting the Product entity.

If you like this article, please subscribe.
[x_subscribe form=”277″]