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 to share