The SqlParameter is already contained by another SqlParameterCollection

Question

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 already contained by another SqlParameterCollection"

Here's my repository code:

var db = (DbContext)DataContext;
        const string sqlString =
            @"            
            WITH UserFollowerList
            AS 
            ( 
            SELECT uf.FollowId
            FROM UserFollow uf 
            WHERE uf.UserId = @UserId
            )
            SELECT * FROM UserFollowerList uf
            INNER JOIN [User] u ON uf.FollowId = u.UserId
            WHERE IsDeleted = 0
            "
            ;

        var userIdParam = new SqlParameter("UserId", SqlDbType.Int) {Value = userId};

        var userList =
            db.Database.SqlQuery<User>(sqlString, userIdParam)
            .ToPagedList(pageIndex, pageSize);

        return userList;

But when I call the ToList extension on the SqlQuery statement it works fine:

var userList = db.Database.SqlQuery<User>(sqlString, userIdParam).ToList();

PagedList code:

private PagedList(IQueryable<T> source, int pageIndex, int pageSize)
    {
        TotalItemCount = source.Count();

        PageSize = pageSize;
        PageIndex = pageIndex;
        PageCount = TotalItemCount > 0 ? (int)Math.Ceiling(TotalItemCount / (double)PageSize) : 0;

        HasPreviousPage = (PageIndex > 0);
        HasNextPage = (PageIndex < (PageCount - 1));
        IsFirstPage = (PageIndex <= 0);
        IsLastPage = (PageIndex >= (PageCount - 1));

        ItemStart = PageIndex * PageSize + 1;
        ItemEnd = Math.Min(PageIndex * PageSize + PageSize, TotalItemCount);

        // add items to internal list
        if (TotalItemCount > 0)
            Data = pageIndex == 0 ? source.Take(pageSize).ToList() : source.Skip((pageIndex) * pageSize).Take(pageSize).ToList();
    }

I've already the solution below without any success:

var param = new DbParameter[] { new SqlParameter { ParameterName = "UserId", Value = userId }

What can I do to fix the error I'm experiencing?

Answer 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.