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