Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Part 9 - Overriding stored procedure defaults with entity framework code first approach

Suggested Videos
Part 6 - How to seed database with test data using entity framework
Part 7 - Using stored procedures with entity framework
Part 8 - Using stored procedures with entity frameowrk code first approach



In this video we will discuss, changing the default Insert, Update and Delete stored procedure names that are auto-generated by entity framework code first approach. This is continuation to Part 8. Please watch Part 8 before proceeding.



public class EmployeeDBContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>().MapToStoredProcedures();
        base.OnModelCreating(modelBuilder);
    }
}

By default, the code above generates the following 3 stored procedures for Inserting, Updating and Deleting Employee objects.
Employee_Insert
Employee_Update
Employee_Delete

If you want to override or change the default names of auto-generated stored procedures, change the code in EmployeeDBContext class as shown below.
public class EmployeeDBContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>()
            .MapToStoredProcedures(p => p.Insert(x => x.HasName("InsertEmployee")));
        modelBuilder.Entity<Employee>()
            .MapToStoredProcedures(p => p.Update(x => x.HasName("UpdateEmployee")));
        modelBuilder.Entity<Employee>()
            .MapToStoredProcedures(p => p.Delete(x => x.HasName("DeleteEmployee")));

        base.OnModelCreating(modelBuilder);
    }
}

At this point delete the Sample database and run WebForm1 again. Notice that the generated stored procedures now have the names we specified.
overriding the default stored procedure names in entity framework code first

The above code can also be rewritten as shown below
public class EmployeeDBContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>().MapToStoredProcedures
            (p => p.Insert(i => i.HasName("InsertEmployee"))
                    .Update(u => u.HasName("UpdateEmployee"))
                    .Delete(d => d.HasName("DeleteEmployee"))
            );
        base.OnModelCreating(modelBuilder);
    }
}

The default parameter names of the stored procedures can also be changed using the following code.
public class EmployeeDBContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>().MapToStoredProcedures
            (p => p.Insert(i => i.HasName("InsertEmployee")
                                    .Parameter(n => n.Name, "EmployeeName")
                                    .Parameter(n => n.Gender, "EmployeeGender")
                                    .Parameter(n => n.Salary, "EmployeeSalary"))
                    .Update(u => u.HasName("UpdateEmployee")
                                    .Parameter(n => n.ID, "EmployeeID")
                                    .Parameter(n => n.Name, "EmployeeName")
                                    .Parameter(n => n.Gender, "EmployeeGender")
                                    .Parameter(n => n.Salary, "EmployeeSalary"))
                    .Delete(d => d.HasName("DeleteEmployee")
                                    .Parameter(n => n.ID, "EmployeeID"))
            );
        base.OnModelCreating(modelBuilder);
    }
}

At this point drop the Sample database and run WebForm1 again. Notice that the stored procedure parameters have the names we specified.
overriding the default stored procedure parameter names in entity framework code first

Entity Framework Tutorial

2 comments:

  1. Very Nicely explained. But one double in earlier session we have enabled Item_Inserted event of gridview to refresh Grid once data inserted. But in this session without having this event data is refreshing upon insert how its taken care automatically?

    ReplyDelete
  2. Hi Venkat , I have a couple of questions in this , where are you initializing the p,n,i,u,d. Please kindly let me know.

    ReplyDelete

It would be great if you can help share these free resources