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

Question

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

When a query times out, I'm attempting to utilize the DBExecutionStrategy to retry it, but I receive the error message "The SqlParameter is already included by another SqlParameterCollection." I employ EF6.

My Question

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

My plan of action is:

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 history:

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 be done to avoid this mistake? Can the execution strategy be utilized with Database.SqlQuery?

Bruno Pessanha

Related Questions

View more on Stack Overflow

Tamas · Accepted Answer · 11/11/2015 10:02:50 PM

The gist of it is that you cannot do this (if your command has parameters).

The lengthy response: This reproduces the problem to a minimum. I removed the execution plan from the illustration and substituted a loop for it. The ObjectContext, notably in the, implements this logic.ExecuteStoreQueryInternalAsync method. The issue appears to be that acommand.Parameters.Clear() In the cleanup part, the call is absent.

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();
        }
    }
}

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

Question

Fastest Entity Framework Extensions

Accepted Answer

Related Questions

Fastest Entity Framework Extensions

Performance

Expression Evaluator

Related