Exception during transactions using EF's DbContext.ExecuteSqlCommand()

Question

Using a try-catch structure i'm trying to figure what to do if an exception is caught in any point of the transaction. Below one sample of code:

try
{
   DbContext.ExecuteSqlCommand("BEGIN TRANSACTION");        //Line 1
   DBContext.ExecuteSqlCommand("Some Insertion/Deletion Goes Here"); //Line 2
   DbContext.ExecuteSqlCommand("COMMIT");                   //Line 3
}
catch(Exception)
{
}

If the expection was caught executing 'Line 1' nothing must be done besides alerting the error. If it was caught executing the second line i don't know if i need to try to rollback the transaction that was sucessfully opened and the same occurs in case something went wrong with the third line.

Should i just send a rollback anyway? Or send all the commands straight to the bank in a single method call?

Inside the try-catch there's a loop performing many transactions like the one in the sample (and i need lots of small transactions instead of just a big one so i can reuse the SQL's '_log' file properly and avoid it to grow unnecessarily).

If any of the transactions go wrong i'll just need to delete them all and inform what happen't, but i can't turn that into one big transaction and just use rollback otherwise it will make the log file grow up to 40GB.

score 6 · Accepted Answer · answered Jul 11 '14 at 19:23

Think this will help:

using (var ctx = new MyDbContext())
{
    // begin a transaction in EF – note: this returns a DbContextTransaction object
    // and will open the underlying database connection if necessary
    using (var dbCtxTxn = ctx.Database.BeginTransaction())
    {
       try
       {
            // use DbContext as normal - query, update, call SaveChanges() etc. E.g.:
           ctx.Database.ExecuteSqlCommand(
               @"UPDATE MyEntity SET Processed = ‘Done’ "
               + "WHERE LastUpdated < ‘2013-03-05T16:43:00’");

           var myNewEntity = new MyEntity() { Text = @"My New Entity" };
           ctx.MyEntities.Add(myNewEntity);
           ctx.SaveChanges();

           dbCtxTxn.Commit();
       }
       catch (Exception e)
       {
           dbCtxTxn.Rollback();
       }
    } // if DbContextTransaction opened the connection then it will close it here
}

taken from: https://entityframework.codeplex.com/wikipage?title=Improved%20Transaction%20Support

Basically the idea of it is your transaction becomes part of the using block, and within that you have a try/catch with the actual sql. If anything fails within the try/catch, it will be rolled back

Worth noting this is only with EF 6. For older versions of EF, you should use TransactionScope instead (see http://stackoverflow.com/questions/22382892/database-begintransaction-vs-transactions-transactionscope) — Tim Copenhaver, Jul 11 '14 at 19:35
If i'm not mistaken with the terminology this is kind of a transaction scope, but is this useful when i don't need to work with entities at all? What's the difference of your answer to writing the whole transaction as a single query 'begin transaction /n Insert stuff /n commit' ? — v1n1akabozo, Jul 11 '14 at 19:49
Well as you can see from the example, he's not working with entities but doing inline sql. The benefit to doing it this way is it cancels out your question - you don't have to worry about how/where the catch occurred, it will be rolled back regardless. — Kritner, Jul 11 '14 at 19:52
and what happens in case the transaction couldn't be opened? The using block will be skipped? — v1n1akabozo, Jul 11 '14 at 19:55

Matt Caton · Answer 2 · 2014-07-12T10:54:06.830

As of Entity Framework 6, ExecuteSqlCommand is wrapped with its own transaction as explained here: http://msdn.microsoft.com/en-gb/data/dn456843.aspx

Unless you explicitly need to roll multiple sql commands into a single transaction, there is no need to explicitly begin a new transaction scope.

With respect to transaction log growth and assuming you are targeting Sql Server then setting the transaction log operation to simple will ensure the log gets recycled between checkpoints.

Obviously if the transaction log history is not being maintained across the entire import, there is no implicit mechanism to rollback all the data in case of failure. Keeping it simple, I would probably just add a 'created' datetime field to the table and delete from the table based on a filter to the created field if I needed to delete all rows in case of error.

i said to use it 'properly' hahaha and i said so because when i was trying to use a single query to do the task the log file would grow to near 40Gb, when it used to sit around 6gb so i'm creating little batches that will reuse the log file when the previous operation is comitted thus not expanding the log file more than needed. The recovery model was already set up to simple but this immense query was driving me nuts. thanks for your answer anyway =] — v1n1akabozo, Jul 12 '14 at 01:14

Exception during transactions using EF's DbContext.ExecuteSqlCommand()

2 Answers2