Getting "The SqlParameter is already contained by another SqlParameterCollection." error while using SqlQuery command

Question

I am trying to parameterize a dynamic query and run it using SqlQuery method in Entity Framework code first.

The first time I execute SqlQuery it works as expected so I am sure there is nothing wrong with query or parameters but immediately I execute the same command with the same parameters second time and I get this error

"The SqlParameter is already contained by another SqlParameterCollection."

Since I am already using ToList() method here, I have no idea what the cause could be!

Here is the simulated code.

using (var context = Common.GetDbContext())
   {
        var parameters = new List<SqlParameter>();

         //populating parameters here...

         var sqlQuery = "Select * from MyTable where UserId=@p1 and And Active=@p2";

         // first time
         var result = context.Database.SqlQuery<ResultType>(sqlQuery, parameters.ToArray()).ToList();
         //second time
         result = context.Database.SqlQuery<ResultType>(sqlQuery, parameters.ToArray()).ToList();
  }

Any idea?

Answer 1

Hi SqlParameter is clonable. Try this:

result = context.Database.SqlQuery<ResultType>(sqlQuery, parameters.Select(x => x.Clone()).ToArray()).ToList();

See https://msdn.microsoft.com/en-us/library/vstudio/bb338957%28v=vs.100%29.aspx