The SqlParameter Is Already Contained By Another SqlParameterCollection

December 07, 2022 Post a Comment

I'm using EF DbContext SqlQuery to get a list of paged objects using PagedList (https://github.com/TroyGoode/PagedList) and I'm getting the following error: 'The SqlParameter is al

Solution 1:

FYI I just saw this exact same error message when using an EF 5 DbContext to call context.ExecuteQuery<my_type>(...); with an array of SqlParameters, where my_type had a string but the SQL statement was returning an int for one of the parameters.

The error was really in the return mapping, but it said the SqlParameter was to blame, which threw me off for a little while.

Solution 2:

When using a generic call to SqlQuery such as db.Database.SqlQuery you must iterate to the last record of the returned Set in order for the result set and associated parameters to be released. PagedList uses source.Take(pageSize).ToList() which will not read to the end of the source set. You could work around this by doing something like foreach(User x in userList) prior to returning the result.

Solution 3:

I tried the following solution from Diego Vega at http://blogs.msdn.com/b/diego/archive/2012/01/10/how-to-execute-stored-procedures-sqlquery-in-the-dbcontext-api.aspx and it worked for me:

 var person = context.Database.SqlQuery<Person>(
 "SELECT * FROM dbo.People WHERE Id = {0}", id);

Solution 4:

When you are using parameters on (SqlQuery or ExecuteSqlCommand) you can't use theme by another query until old query dispose. in PagedList method you use "source.Count();" at first and the end line you are using "source" again. that's not correct. you have 2 solution. 1- send param to PagedList Method and new theme for each using SqlQuery or ExecuteSqlCommand 2-remove PagedList and send your paging param to SqlQuery or ExecuteSqlCommand like this :

   const string sqlString =
        @"            
        WITH UserFollowerList
        AS 
        ( 
        SELECT uf.FollowId,ROW_NUMBER() OVER(ORDER BY uf.FollowId ) RowID
        FROM UserFollow uf 
        WHERE uf.UserId = @UserId
        )
        SELECT * FROM UserFollowerList uf
        INNER JOIN [User] u ON uf.FollowId = u.UserId
        WHERE IsDeleted = 0 and RowID BETWEEN (((@PageNumber- 1) *@PageSize)+ 1) AND (@PageNumber * @PageSize))
        "
        ;

Solution 5:

Just encountered this exception even though it was my first query to the database with a single param. And having the Context in a 'using'. When I 'hardcoded' the queryparameter valies into the string it worked correct for some reason. But as soon as I used SqlParameter it gave me the "The SqlParameter is already contained by another SqlParameterCollection"

This didn't work:

context.Database.SqlQuery<int?>(query, new SqlParameter("@TableName", tableName));

This did:

context.Database.SqlQuery<int>(query, new SqlParameter("@TableName", tableName));

The difference being the return type int? vs int. So for anyone reading this. Please also check your return type of the SqlQuery even when you're sure it should work.

Lab Sql