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

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

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

"The SqlParameter is already contained by another SqlParameterCollection."

Since I'm already using the method ToList()

here, I don't know what the reason might 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?

+3


source to share


1 answer


Hi SqlParameter is clonable. Try the following:

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

+5


source







All Articles