CODEWITHSUNDEEP
c#sql-serverentity-frameworktransactionsentity-framework-6
Tom Maher
Tom Maher

Reputation: 2054

Entity Framework 6 Handle Transaction At Database Level

I have a stored procedure that uses transactions at a database level (the transactions are handled within the stored procedure itself). This means I need to tell Entity Framework not to handle transactions.

Now using database first model I can import the stored procedure and have an auto generated context class that looks like the following:

 public virtual int MyStoredProcedure()
 {         
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("MyStoreProcedure");
 }

I can add my own method to the DbContext class that looks like the following:

public virtual int MyStoredProcedureWithoutTransaction()
{
    this.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "MyStoredProcedure")
}

My question is does the following code also behave the same as the MyStoredProcedureWithoutTransaction() in terms of transaction management:

MyContext context = new MyContext();
context.Database.UseTransaction(null);
context.MyStoredProcedure();

If it does I would prefer to use the second version as it will have the advantage of using the auto generated code from Entity Framework.

Upvotes: 1

Views: 1100

Answers (1)

Tom Maher
Tom Maher

Reputation: 2054

If anyone comes across this post and is wondering the answer is No!

context.Database.UseTransaction(null);

Is not the same as using TransactionalBehavior.DoNotEnsureTransaction on the ExecuteSqlCommand overloaded method.

However the following does work and can be used how I originally wanted:

((IObjectContextAdapter)_context).ObjectContext.ContextOptions.EnsureTransactionsForFunctionsAndCommands = false;

In the application I simply created a base repository class as follows:

public abstract class BaseEntityFrameworkNonTransactionRepository<T> where T : DbContext, new()
{
    protected T _context;

    protected BaseEntityFrameworkNonTransactionRepository()
    {
        _context = new T();
        ((IObjectContextAdapter)_context).ObjectContext.ContextOptions.EnsureTransactionsForFunctionsAndCommands = false;
    }
}

Upvotes: 3

Related Questions