Entity Framework 4, execute a writing stored procedure inside a TransactionScope

Suppose you have a stored procedure that performs multiple insert, update or delete. You may do this in for many reasons, probably for performance, to avoid reading a large object graph with no need. In my case I needed to delete a large object graph. So I’ve writed my powerful stored procedure and created a function import. Here’s a really simplified example:


using(var tx = new TransactionScope())
{
  using(var ctx = new EntityContext())
  {
      ctx.FunctionImportWithDeleteStoredProcedure(parameter);
  }
  tx.Complete();
}

Seems ok isn’t it? Well you will get a TransactionAbortedException: “The transaction operation cannot be performed because there are pending requests working on this transaction.”

Why? Function import in EF are thought to be used to perform operations and retrieve values. It currently lacks the functionality of no return type (Yes there is the “None” return type option in the function import form, I encourage you to try it.). My Stored procedure was prepared for this, I return the rows affected.

Anyway, what can we do? We must evaluate the result, even if we don’t need it:


using(var tx = new TransactionScope())
{
  using(var ctx = new EntityContext())
  {
     // Evaluating the result does the magic!!
     ctx.FunctionImportWithDeleteStoredProcedure(parameter)
                              .SingleOrDefault(); 
  }
  tx.Complete();
}

Thats it. A bit tricky yes, but it works. I realised afterwards that in my case probably the right solution would be to map the store procedure to my entity remove operation, to remove the entity and the entire related object Graph. See Map Modification Functions to Stored Procedures
Many thanks to Danny Simmons for his answers.

Advertisements

3 thoughts on “Entity Framework 4, execute a writing stored procedure inside a TransactionScope

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s