该SqlParameter已包含在另一个SqlParameterCollection中 [英] The SqlParameter is already contained by another SqlParameterCollection

问题描述

我正在使用EF DbContext SqlQuery使用PagedList获取分页对象的列表( https://github.com/TroyGoode/PagedList )，并且出现以下错误:

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:

该SqlParameter已被另一个SqlParameterCollection包含"

"The SqlParameter is already contained by another SqlParameterCollection"

这是我的存储库代码:

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;

但是当我在SqlQuery语句上调用ToList扩展名时，它工作正常:

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

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

PagedList代码:

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?

推荐答案

仅供参考，当使用EF 5 DbContext调用带有SqlParameters数组的context.ExecuteQuery<my_type>(...);时，我刚刚看到了完全相同的错误消息，其中my_type具有string但是SQL语句返回其中一个参数的int.

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.

错误实际上是在返回映射中，但是它说应该归咎于SqlParameter，这使我离开了一会儿.

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

这篇关于该SqlParameter已包含在另一个SqlParameterCollection中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！