EF6 Retrying procedure throws "The SqlParameter is already contained by another SqlParameterCollection" for SqlQuery command

ado.net c# entity-framework entity-framework-6 sql-server

Question

I'm trying to use the DBExecutionStrategy to retry queries that have timed out, but when the time out happens I get the error "The SqlParameter is already contained by another SqlParameterCollection". I'm using EF6.

My Query:

using (var ctx = new EntityModel())
{
    IEnumerable<ItemResponse> items= ctx.Database.SqlQuery<ItemResponse>(
           "spItemListGet @UserID", new SqlParameter("@UserID", UserID)
    ).ToList();
}

My Execution Strategy:

protected override bool ShouldRetryOn(Exception ex)
{
    bool retry = false;

    SqlException sqlException = ex as SqlException;
    if (sqlException != null)
    {
        int[] errorsToRetry =
        {
            -2,     //Timeout
        };
        if (sqlException.Errors.Cast<SqlError>().Any(x => errorsToRetry.Contains(x.Number)))
        {
            retry = true;
        }
        else
        {
            throw ex; //dont retry
        }
    }

    return retry;
}

The stack trace:

System.ArgumentException: The SqlParameter is already contained by another SqlParameterCollection.
   at System.Data.SqlClient.SqlParameterCollection.Validate(Int32 index, Object value)
   at System.Data.SqlClient.SqlParameterCollection.AddRange(Array values)
   at System.Data.Entity.Core.Objects.ObjectContext.CreateStoreCommand(String commandText, Object[] parameters)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryInternal[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__64()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__63()
   at System.Data.Entity.Infrastructure.DbExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliably[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery[TElement](String commandText, ExecutionOptions executionOptions, Object[] parameters)
   at System.Data.Entity.Internal.InternalContext.<>c__DisplayClass14`1.<ExecuteSqlQuery>b__13()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

What could it be done to prevent this error? Can Database.SqlQuery be used for with the execution strategy?

1
3
11/9/2015 11:39:49 AM

Accepted Answer

The short answer: No, you can't do this (if your command has parameters).

The long answer: Here is a minimal repro of the issue. I stripped out the execution strategy from the picture, and faked it with a loop instead. This logic is implemented in the ObjectContext, specifically in the ExecuteStoreQueryInternalAsync method. The problem seems to be that a command.Parameters.Clear() call is missing from the cleanup part.

static void Main(string[] args)
{
    TestQuery();
}

private static void TestQuery()
{
    using (var ctx = new ProductContext())
    {
        var parameter = new SqlParameter("@ID", 1);
        var commandText = "select * from product where ProductId = @ID";

        Action a = () =>
        {
            IDbCommand command = new SqlCommand();
            command.CommandText = commandText;
            command.Parameters.Add(parameter);

            command.Connection = ctx.Database.Connection;
            if (command.Connection.State != ConnectionState.Open)
            {
                command.Connection.Open();
            }

            var reader = command.ExecuteReader();
            try
            {
                throw new Exception();
                while (reader.Read())
                {
                    var pId = reader["ProductID"];
                }
                reader.Close();
            }
            catch (Exception exc)
            {
                //for simplification, we just swallow this error, but in reality the connection error
                //would reach the IDbExecutionStrategy, and would do a retry. Instead we fake the retry
                //with a loop below
            }
            finally
            {
                reader.Dispose();

                //command.Parameters.Clear();  <--------- THIS LINE IS MISSING FROM EF
                command.Dispose();
            }
        };

        for (int i = 0; i < 2; i++) // we fake the retry with a loop now
        {
            a();
        }
    }
}
4
11/11/2015 10:02:50 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow