Whats new in EF 6

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Stored Procedures for CUD
EF 6 gives code-first developers something developers using the visual designer have always had: The ability to use stored procedures when you call SaveChanges to perform the updates to the tables represented by the entity classes. But don’t get your hopes up: There’s still nothing in code-first development like the function import the visual designer provides for using stored procedures (nor does EF Power Tools generate any support for stored procedures, at least as of Beta 4).

If your stored procedures have the right names and parameters, you can tell EF6 to use your stored procedures with just one line of code in the DbContext OnModelCreating method. The following example directs EF to use stored procedures for all updates made to a Product entity:

1
2
3
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  modelBuilder.Entity<Product>().MapToStoredProcedures();
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  modelBuilder.Entity<Product>().MapToStoredProcedures();

This feature gives you an enormous amount of flexibility: you can do anything you want or need in the stored procedure, rather than just the plain-old updates EF provides.

However, you probably aren’t going to be able to get away with that single line of code. In order for this to work, EF assumes a great deal about your stored procedures. To begin with, for my example, EF assumes I have three stored procedures called Product_Update, Product_Insert and Product_Delete. The problem is that if you’re interested in this feature, you’re already using stored procedures to manage your create/update/delete (CUD) activities, and these aren’t the names of your stored procedures. Fortunately, you can specify the names of the stored procedures you are using with just a little more code. The following code sets the names of the CUD stored procedures to spAddProduct, spUpdateProduct and spDeleteProduct:

1
2
3
4
5
6
modelBuilder.Entity<Product>().MapToStoredProcedures(sp =>
{
  sp.Insert(proc => proc.HasName("spAddProduct"));
  sp.Update(proc => proc.HasName("spUpdateProduct"));
  sp.Delete(proc => proc.HasName("spDeleteProduct"));
});
modelBuilder.Entity<Product>().MapToStoredProcedures(sp =>
{
  sp.Insert(proc => proc.HasName("spAddProduct"));
  sp.Update(proc => proc.HasName("spUpdateProduct"));
  sp.Delete(proc => proc.HasName("spDeleteProduct"));
});

You’ll notice I configured all three CUD operations. If you use a stored procedure for any CUD operation, you must use stored procedures for all three CUD operations. This means that if you use MapToStoredProcedures and don’t specify a stored procedure for the delete operation, EF6 will go looking for a stored procedure with the default name.

You can also customize the parameters passed to the stored procedures and the values returned from them. It’s not unusual, for instance, for a stored procedure that inserts new rows to return the key generated by the database for the newly-added row. This is all the code needed to extend my definition to specify that my stored procedure returns a result called id that’s mapped to the ProductId property on my Product object:

1
2
3
4
modelBuilder.Entity<Product>().MapToStoredProcedures(sp =>
{
  sp.Insert(proc => proc.HasName("spAddProduct")
    .Result(r => r.ProductID,"id"));
modelBuilder.Entity<Product>().MapToStoredProcedures(sp =>
{
  sp.Insert(proc => proc.HasName("spAddProduct")
    .Result(r => r.ProductID,"id"));

Similar code lets me customize the parameters the stored procedure is passed, accept the RowsAffected result and link to related tables. As you can imagine, this level of customization can result in a lot of code. Once you’ve written the code that defines a table’s stored procedures, you’ll want to put that code somewhere where you can integrate into any application.


Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.