Ef6: Retrying queries or SQL commands fails with "The SqlParameter is already contained by another SqlParameterCollection"

[gaurav-pra]

Hello All,

I came to know from issue tracker that EF guys fix issue on "The Sql query is attempt to be retried, but when a command is created and Sql parameter are
added, it fails as we are reusing the SQL parameters passed to the query." with changeset-1072839. Does this fix, will also fix the
same issue while retrying "DbCotext.Database.ExecuteSqlCommand" method having sql parameters.

I've check my application with EF 6.1.3 and
even today's nighly build of EF but when i'm using "DbCotext.Database.ExecuteSqlCommand" method having sql parameters passed as argument
while retrying will giving me "The SqlParameter is already contained by another SqlParameterCollection" exception.
Is this seperate issue in EF?

Thanks,

[AndriySvyryd]

Other users have reported this with the following stack traces:

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.<>c__DisplayClass59.b__58()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransactionT

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.<>c__DisplayClass5e.b__5c()
   at System.Data.Entity.Core.Objects.ObjectContext.d__3d1.MoveNext() --- End of stack trace from previous location where exception was thrown ---    at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)    at System.Data.Entity.Infrastructure.DbExecutionStrategy.<ProtectedExecuteAsync>d__91.MoveNext()
--- End of stack trace from previous location where exception was thrown ---

[divega]

The same issue was fixed for other APIs in https://github.com/aspnet/EntityFramework6/commit/107283972666babbff10fb7409298f39200acb09.

[ghost]

Is there a work-around for those like me that are using 6.1.3 with no option to install a beta version of 6.2.0?

[AndriySvyryd]

@glozanomoran There are no good workarounds. You would need to get the DbConnection and create and execute the command manually and wrap this in a ExecutionStrategy.Execute call, see https://msdn.microsoft.com/en-us/library/dn307226(v=vs.113).aspx

[ghost]

Any idea when EF 6.2 will be released? For now I suspend the execution strategy for the Database.SqlQuery() and Database.ExecuteSqlCommand() but my initial plan was to actually add the retrying strategy for the operations behind these two methods.

[AndriySvyryd]

@glozanomoran We don't have a date for the release yet, but we are working on it.

[ghost]

Thank you for your responses and patience. It's just that I saw in the roadmap that you planned to release EF 6.2 in Q2 2017:
https://github.com/aspnet/EntityFramework6/wiki/Roadmap

I just have to figure out whether to wait for the release of EF6.2 or to find a work-around.

[ghost]

Just wanted to say that we have upgraded to Entity Framework 6.2 and I wanted to thank you for fixing this issue in this release.

[takkip]

Hello
The fix so far works for timeout error in EF 6.2 but I'm still experiencing the same System.ArgumentException: The SqlParameter is already contained by another SqlParameterCollection error for deadlock error. Stack trace is the same as what is quoted above.

Stepping through the code I can see deadlock error is thrown from the line:

bufferedDataReader.Initialize(itemCollection.ProviderManifestToken, service, shaperFactory.ColumnTypes, shaperFactory.NullableColumns);

Think the second catch block in ExecuteStoreQueryInternal also needs the same command.Parameters.Clear() and command.Dispose()?

try
{
StoreItemCollection itemCollection = (StoreItemCollection) this.MetadataWorkspace.GetItemCollection(DataSpace.SSpace);
DbProviderServices service = DbConfiguration.DependencyResolver.GetService((object) itemCollection.ProviderInvariantName);
bufferedDataReader = new BufferedDataReader(reader);
bufferedDataReader.Initialize(itemCollection.ProviderManifestToken, service, shaperFactory.ColumnTypes, shaperFactory.NullableColumns);
reader = (DbDataReader) bufferedDataReader;
}
catch
{
bufferedDataReader?.Dispose();
throw;
}

Same for ExecuteStoreQueryInternalAsync.

Thanks.

[ghost]

What a coincidence because Friday we had the exact same error. We had an issue where a script with an error was run from SQL Server Management Studio and the started transaction was not committed nor rolled back and because of this one of the tables was locked and this was leading to the exact same error as described by takkip.

[takkip]

Hi Arthur,
I'm new to Github. Saw you removed the issue from 6.2.0 milestone. Do we need to reopen this so that it will get fixed in the future?

Thanks.

[ajcvickers]

@takkip I removed it so we will look at it again, but thanks to @AndriySvyryd for also re-opening since that also tends to help. 😄

[faikozgur]

I am having the same exception but I can reproduce it in different way. This is not related to retrying strategy.

            var exp = _nwind.Database.SqlQuery<Order>("SELECT * FROM Orders WHERE Freight> @Freight AND OrderDate > @OrderDate ", parms).AsQueryable();
// below is the part of the another component. It gets the expression and does paging operations for the grid
            var count = exp.Count(); 
            var page = exp.Skip(10).Take(20);
            var list = page.ToList(); 

and stacktrace is as follows:

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.ExecuteStoreQueryInternalTElement
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass651.<ExecuteStoreQueryReliably>b__64() at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass651.<ExecuteStoreQueryReliably>b__63() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliablyTElement
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryTElement
at System.Data.Entity.Internal.InternalContext.<>c__DisplayClass141.<ExecuteSqlQuery>b__13() at System.Data.Entity.Internal.LazyEnumerator1.MoveNext()
at System.Linq.Enumerable.WhereEnumerableIterator1.MoveNext() at System.Linq.Enumerable.<SkipIterator>d__311.MoveNext()
at System.Linq.Enumerable.d__251.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)

[timritzer]

@faikozgur That looks like a different issue. You can test with my linked PR and see if it resolves for you, but manual queries plus additional IQueryable operations are a bit sketchy in ef6. I recommend you move to ef core for that, or perform your paging in your query yourself.

[Jehoel]

I'm experiencing the same issue. This exception happened this morning:

• Not using any retry logic (as far as I know).
• Using EntityFramework NuGet package, version 6.4.4.
• Also using EntityFramework.CodeFirstStoreFunctions version 1.2.0. I don't know if my code is making use of this library though.

Here's my DbContext method: Note that the code was not using .ConfigureAwait(false) - if that matters (I've since added it, but for unrelated reasons).

public async Task<List<SetDocumentItemsReturnModel>> SetDocumentItemsAsync( int? personId, int? documentId, bool? deleteUnmatched, DataTable documentItems )
{
    SqlParameter personIdParam = new SqlParameter { ParameterName = "@personId", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = personId.GetValueOrDefault(), Precision = 10, Scale = 0 };
    if( !personId.HasValue )
        personIdParam.Value = DBNull.Value;

    SqlParameter documentIdParam = new SqlParameter { ParameterName = "@documentId", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = documentId.GetValueOrDefault(), Precision = 10, Scale = 0 };
    if( !documentId.HasValue )
        documentIdParam.Value = DBNull.Value;

    SqlParameter deleteUnmatchedParam = new SqlParameter { ParameterName = "@deleteUnmatched", SqlDbType = SqlDbType.Bit, Direction = ParameterDirection.Input, Value = deleteUnmatched.GetValueOrDefault() };
    if( !deleteUnmatched.HasValue )
        deleteUnmatchedParam.Value = DBNull.Value;

    SqlParameter documentItemsParam = new SqlParameter { ParameterName = "@documentItems", SqlDbType = SqlDbType.Structured, Direction = ParameterDirection.Input, Value = documentItems, TypeName = "dbo.DocumentItemList" };
    if( documentItemsParam.Value == null )
        documentItemsParam.Value = DBNull.Value;

    var procResultData = await this.Database
        .SqlQuery<SetDocumentItemsReturnModel>("EXEC [dbo].[SetDocumentItems] @personId, @documentId, @deleteUnmatched, @documentItems", personIdParam, documentIdParam, deleteUnmatchedParam, documentItemsParam)
        .ToListAsync();

    return procResultData;
}

Here's my stack-trace. There's no InnerException set:

   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.<ExecuteStoreQueryInternalAsync>d__195`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter`1.GetResult()
   at System.Data.Entity.Core.Objects.ObjectContext.<ExecuteInTransactionAsync>d__156`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter`1.GetResult()
   at System.Data.Entity.Infrastructure.DbExecutionStrategy.<ProtectedExecuteAsync>d__21`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter`1.GetResult()
   at System.Data.Entity.Core.Objects.ObjectContext.<ExecuteStoreQueryReliablyAsync>d__194`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter`1.GetResult()
   at System.Data.Entity.Internal.LazyAsyncEnumerator`1.<FirstMoveNextAsync>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Data.Entity.Infrastructure.IDbAsyncEnumerableExtensions.<ForEachAsync>d__2`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MyProject.Data.MyDbContext.<SetDocumentItemsAsync>d__380.MoveNext() in C:\git\me\MyProject.Common\Model\MyDbContext.cs:line 2091
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MyProject.Pages.Ajax.SetsItemsHandler.<UpsertItemAsync>d__8.MoveNext() in C:\git\me\MyProject.Application\Pages\Ajax\Items\SetItemHandler.cs:line 219"