EF6重试过程为SqlQuery命令抛出“SqlParameter已被另一个SqlParameterCollection包含”
EF6重试过程为SqlQuery命令抛出“SqlParameter已被另一个SqlParameterCollection包含”
提问于 2015-11-03 15:40:59
我试图使用DBExecutionStrategy重试已超时的查询，但当超时发生时，我会得到“SqlParameter已被另一个SqlParameterCollection包含”的错误。我在用EF6。
我的问题是：
using (var ctx = new EntityModel())
{
IEnumerable<ItemResponse> items= ctx.Database.SqlQuery<ItemResponse>(
"spItemListGet @UserID", new SqlParameter("@UserID", UserID)
).ToList();
}我的执行策略：
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;
}堆栈跟踪：
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)如何防止这一错误？Database.SqlQuery可以与执行策略一起使用吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-11-11 22:02:50
简短的回答是:不，您不能这样做(如果您的命令有参数)。
长话短说:这是这个问题的最低限度的再现。我从图片中去掉了执行策略，然后用循环来伪造它。这个逻辑是在ObjectContext中实现的，特别是在ExecuteStoreQueryInternalAsync方法中。问题似乎是清理部分缺少了一个command.Parameters.Clear()调用。
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();
}
}
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33503212
复制相关文章
相似问题
腾讯云开发者
