none
EF 5.0 DbContext Code First and transaction management RRS feed

  • Question

  • hello everyone

    I am using EF5 with DbContext, Code First, POCO, etc, and I need to be able to control SQL Server (and also Oracle) transactions precisely. In some cases I may need to use distributed transactions (i.e. SQL> Begin Distributed Transaction).

    I am using (TransactionScopetransaction = newTransactionScope())

    then calling transaction.Complete();

    this approach (according to SQL Profiler) does the right thing under most trivial use cases.

    But appears to call Rollback as soon as the stored procedure or SQL statement Trows any exception, even non-fatal one.

    By the time my code is in Catch () block, the transaction has been rolled back (SQL Profiler).

    In other words I am not able to repair data, retry and continue, I have to redo everything from the beginning.

    Sometime I need to explicitly call Rollback, even if database did not throw any errors (such as no data found).

    Should I call transaction.Dispose() to call a Rollback?

    Is there an explicit way of starting, committing and rolling back transactions using DbContext ?

    is there SET TRANSACTION ISOLATION LEVEL control at DbContext/Code First workflow ?

    Do I just need to use manual approach in all cases like so:

    context.Database.ExecuteSqlCommand("BEGIN/COMMIT/SET/ROLLBACK")

    thanks!


    Yuri Budilov

    Thursday, June 20, 2013 8:23 AM

Answers

  • You should be able to use a TransactionScope and specify the IsolationLevel by passing a TransactionOptions enumeration to its constructor. If you need more granular control, you could use a stored procedure for doing things at the database side.

    A call to TransactionScope.Dispose(), either by a using block or by calling the Dispose method explicitly, will rollback the transaction.

    Thursday, June 20, 2013 9:36 AM

All replies

  • You should be able to use a TransactionScope and specify the IsolationLevel by passing a TransactionOptions enumeration to its constructor. If you need more granular control, you could use a stored procedure for doing things at the database side.

    A call to TransactionScope.Dispose(), either by a using block or by calling the Dispose method explicitly, will rollback the transaction.

    Thursday, June 20, 2013 9:36 AM
  • thank you very much.


    Yuri Budilov

    Friday, June 21, 2013 2:11 AM