EntityCommandExecutionException if EF context is created from SqlConnection

0
votes

During a long running batch transaction running mostly with direct SQL statements, I would like to use a EF statement which should run as part of the current transaction.

I looked at the constructors of DbContext and came up with the following approach:

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ResPlannerContext"].ConnectionString))
{
    connection.Open();
    SqlTransaction trans = connection.BeginTransaction();
    using (ResPlannerContext context = new ResPlannerContext(trans.Connection, false))
    {
        var data = context.Activities.Where(x => x.StartDate < DateTime.Today);
        Console.WriteLine("Count: " + data.Count());
    }
}

The good news: It compiles. The bad ones: The following exception pops up:

Message=An error occurred while executing the command definition. See the inner exception for details.
Source=EntityFramework
StackTrace:
   bei System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   bei System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   bei System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
   bei System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   bei System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
   bei System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   bei System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
   bei System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
   bei System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   bei System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
   bei System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__3[TResult](IEnumerable`1 sequence)
   bei System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
   bei System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   bei System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
   bei System.Linq.Queryable.Count[TSource](IQueryable`1 source)
   bei ConsoleApplication3.Program.Main(String[] args) in c:\Users\sreindl\Documents\Visual Studio 2013\Projects\ConsoleApplication3\ConsoleApplication3\Program.cs:Zeile 26.
   bei System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   bei System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   bei Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   bei System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   bei System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   bei System.Threading.ThreadHelper.ThreadStart()
InnerException: System.InvalidOperationException
   _HResult=-2146233079
   _message=ExecuteReader erfordert, dass der Befehl über eine Transaktion verfügt, wenn die dem Befehl zugewiesene Verbindung eine ausstehende lokale Verbindung ist. Die Transaction-Eigenschaft des Befehls wurde nicht initialisiert.
   HResult=-2146233079
   IsTransient=false
   Message=ExecuteReader erfordert, dass der Befehl über eine Transaktion verfügt, wenn die dem Befehl zugewiesene Verbindung eine ausstehende lokale Verbindung ist. Die Transaction-Eigenschaft des Befehls wurde nicht initialisiert.
   Source=System.Data
   StackTrace:
        bei System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
        bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
        bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
        bei System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
        bei System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
        bei System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
        bei System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
        bei System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
        bei System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
        bei System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
        bei System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
        bei System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   InnerException:

The German exception inside (sorry for using german SQL Server :-) means: ExecuteReader requires command to have transaction when connection assigned to command is in pending local trans.

Actually the Transaction property of the internal SqlCommand isn't set.

Do you have any idea how to manage this situation?

2
c#entity-frameworktransactions

2 Answers

1
votes

is the following possible:

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ResPlannerContext"].ConnectionString))
{
    connection.Open();
    using (ResPlannerContext context = new ResPlannerContext(connection, false))
    {
        using (var tran = context.Database.BebinTransaction() ) {
            var data = context.Activities.Where(x => x.StartDate < DateTime.Today);
            Console.WriteLine("Count: " + data.Count());

            tran.Commit();
        }
    }
}

other wise, as stated here

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ResPlannerContext"].ConnectionString))
{
    connection.Open();
    SqlTransaction trans = connection.BeginTransaction();
    using (ResPlannerContext context = new ResPlannerContext(trans.Connection, false))
    {
        context.Database.UseTransaction(trans);

        var data = context.Activities.Where(x => x.StartDate < DateTime.Today);
        Console.WriteLine("Count: " + data.Count());
    }
}
0
votes

I think you should write two separate transactions for this. One should be a connection transaction and another SQLTransaction:

string connString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
using (var conn = new SqlConnection(connString))
{
    conn.Open();
    using (IDbTransaction tran = conn.BeginTransaction())
    {
        try
        {
            // transactional code...
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO Data(Code) VALUES('A-100');";
                cmd.Transaction = tran as SqlTransaction;
                cmd.ExecuteNonQuery();
            }
            tran.Commit();
        }
        catch(Exception ex)
        {
            tran.Rollback();
            throw;
        }
    }
}

Check the post in the below link by S.M.Ahasan Habib where he has explained the above code: http://www.codeproject.com/Articles/690136/All-About-TransactionScope