CODEWITHSUNDEEP
c#entity-frameworkado.netentity-framework-5
krisdyson
krisdyson

Reputation: 3255

Error calling ExecuteSqlCommand the second time on System.Data.Entity.Database

I am using EF5 Code-first and therefore I have a DbContext with a Database property which is of type System.Data.Entity.Database.

The problem I have found is that when you call the same SP more than once with the same parameters, it throws an exception with the message: "The SqlParameter is already contained by another SqlParameterCollection".

This can be demonstrated with the code below. First create a DbContext derivative and connect it to a database. The stored procedure within the code does not have to exist. The first call to the SP will error saying that the SP doesn't exist, however, the second exception is the one we're interested in.

var pa = new SqlParameter[] 
        { 
            new SqlParameter("@Name", SqlDbType.NVarChar) { Value = "test" }
        };
        var dc = new MyWebContext(); // derived from DbContext
        try
        {
            dc.Database.ExecuteSqlCommand("spImport @Name", pa);
        }
        catch { }
        dc.Database.ExecuteSqlCommand("spImport @Name", pa); // fails with "The SqlParameter is already contained by another SqlParameterCollection"

I do need to call the same SP with the same parameters twice or more times, on occasions. This is a valid requirement. My assumption was that calling ExecuteSqlCommand is quite transient and should be possible on the same context multiple times.

It appears that the context is hanging onto the parameter information from the first call which causes a problem with the second.

Here's the stack trace:

at System.Data.SqlClient.SqlParameterCollection.Validate(Int32 index, Object value) at System.Data.SqlClient.SqlParameterCollection.AddRange(Array values) at System.Data.Objects.ObjectContext.CreateStoreCommand(String commandText, Object[] parameters) at System.Data.Objects.ObjectContext.ExecuteStoreCommand(String commandText, Object[] parameters) at System.Data.Entity.Internal.InternalContext.ExecuteSqlCommand(String sql, Object[] parameters) at System.Data.Entity.Database.ExecuteSqlCommand(String sql, Object[] parameters) at EF5ExecuteSqlCommandBugReproduction.Program.Main(String[] args) in c:\EF5ExecuteSqlCommandBugReproduction\EF5ExecuteSqlCommandBugReproduction\Program.cs:line 26 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()

I'd appreciate any guidance. If you think this is a bug with EF then I'll report it. Many thanks

SOLUTION: Wrap up the parameter list creation and ExecuteSqlCommand into an inline function, and just re-invoke that instead of just re-invoking ExecuteSqlCommand. This will ensure a new SqlParameter array is created. var dc = new SpondleWebContext(); // derived from DbContext

        Action act = () =>
        {
            var pa = new SqlParameter[]  { new SqlParameter("@Name", SqlDbType.NVarChar) { Value = "test" } };
            dc.Database.ExecuteSqlCommand("spImport @Name", pa);
        };

        try { act(); } catch { }
        act();

Upvotes: 3

Views: 5083

Answers (2)

danish
danish

Reputation: 5600

I do not have EntityFramework installed here but I am very sure that ExecuteStoreCommand method creates a new DbCommand object each time. The parameter collection you are passing are not created within the framework and are reused by multiple commands. Hence you are getting the errors.

You will have to clone the parameters before the second call.

Upvotes: 3

Pavel Krymets
Pavel Krymets

Reputation: 6293

Don't reuse parameter, it somehow wired up to command, re create it for each call of ExecuteSqlCommand

Upvotes: 0

Related Questions