c# - SqlParameter 已包含在另一个 SqlParameterCollection(EF、SqlQuery、存储过程调用)中

Question

我有一个方法 List<EmployeeView> EmployeeFilter(string myFilter)在我的 Entity Framework DbContext 中。它永远不会结束。当result被执行时，在result.RawView.Message中是

SqlParameter is already contained by another SqlParameterCollection

public List<EmployeeView> EmployeeFilter(string myFilter)
            {
                if (myFilter == null)
                    myFilter = "";

                SqlParameter parameter = new SqlParameter
                {
                    ParameterName = "@filter",
                    IsNullable = true,
                    Direction = ParameterDirection.Input,
                    DbType = DbType.String,
                    Size = 1000,
                    Value = myFilter
                };

                var result = Database.SqlQuery<EmployeeView>("exec EmployeeFilter @filter", parameter);
                List<EmployeeView> employeeList = result.ToList();
                return employeeList;
            }

我试过如下重写方法。但没有任何帮助

1.

var result = Database.SqlQuery<EmployeeView>("exec EmployeeFilter @filter", parameter).ToList();

代替

var result = Database.SqlQuery<EmployeeView>("exec EmployeeFilter @filter", parameter);
                    List<EmployeeView> employeeList = result.ToList();

2.

var parameters = new SqlParameter[] { new SqlParameter("@filter", SqlDbType.NVarChar, 1000) { Value = myFilter} };

或

SqlParameter parameter = new SqlParameter("filter", SqlDbType.NVarChar, 1000);
            parameter.Value = myFilter;

或

SqlParameter parameter = new SqlParameter("@filter", SqlDbType.NVarChar, 1000);
            parameter.Value = myFilter;

或

SqlParameter parameter = new SqlParameter("@filter", myFilter);

存储过程代码:

procedure [dbo].[EmployeeFilter]  
( @filter nvarchar(1000) ) 
as

declare @countFilter as int 

if object_id('tempdb.dbo.#tempFilterValues') is not NULL
drop table #tempFilterValues

select display_term 
into #tempFilterValues
from sys.dm_fts_parser(@filter, 1049, 0, 0)

select @countFilter = count(*) 
from #tempFilterValues


if  @countFilter = 0


--если фильтра нет, то незачем усложнять селект группировкой и поиском
    select e.Id, e.DateOfBirth, e.IdentityCodeChar, e.FirstName, e.SecondName, e.Surname, e.Age, 
    e.ShortAddress, e.Position
    from dbo.EmployeeView e
else
    begin 
    --если в фильтре одна запись
    --появился поиск по like
        if @countfilter = 1
            select e.Id, e.DateOfBirth, e.IdentityCodeChar, e.FirstName, e.SecondName, e.Surname, e.Age, 
            e.ShortAddress, e.Position
            from dbo.EmployeeView e
            inner join #tempFilterValues as ParseString
            on e.Position + e.FirstName + e.SecondName + e.Surname  like '%'+ ParseString.display_term +'%'
        else 
        --если несколько значений в фильтре
        --like
        --появляется группировка
            select e.Id, e.DateOfBirth, e.IdentityCodeChar, e.FirstName, e.SecondName, e.Surname, e.Age, 
            e.ShortAddress, e.Position
            from dbo.EmployeeView e
                inner join #tempFilterValues as ParseString
                on eo.Position+p.FirstName + p.SecondName + p.Surname  like '%'+ ParseString.display_term +'%'
            group by e.Id, e.DateOfBirth, e.IdentityCodeChar, e.FirstName, e.SecondName, e.Surname, e.Age, 
            e.ShortAddress, e.Position
            having count(e.Id) >  1 
    end

异常捕获:

error   "System.Data.Entity.Core.EntityCommandExecutionException: The data reader is incompatible with the specified 'GTIApp.Models.EmployeeView'. A member of the type, 'AgeCount', does not have a corresponding column in the data reader with the same name.\r\n   в System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.GetMemberOrdinalFromReader(DbDataReader storeDataReader, EdmMember member, EdmType currentType, Dictionary`2 renameList)\r\n   в System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.GetColumnMapsForType(DbDataReader storeDataReader, EdmType edmType, Dictionary`2 renameList)\r\n   в System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory.CreateColumnMapFromReaderAndType(DbDataReader storeDataReader, EdmType edmType, EntitySet entitySet, Dictionary`2 renameList)\r\n   в System.Data.Entity.Core.Objects.ObjectContext.InternalTranslate[TElement](DbDataReader reader, String entitySetName, MergeOption mergeOption, Boolean streaming, EntitySet& entitySet, TypeUsage& edmType)\r\n   в System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryInternal[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)\r\n   в System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__64()\r\n   в System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)\r\n   в System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass65`1.<ExecuteStoreQueryReliably>b__63()\r\n   в System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)\r\n   в System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQueryReliably[TElement](String commandText, String entitySetName, ExecutionOptions executionOptions, Object[] parameters)\r\n   в System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreQuery[TElement](String commandText, ExecutionOptions executionOptions, Object[] parameters)\r\n   в System.Data.Entity.Internal.InternalContext.<>c__DisplayClass14`1.<ExecuteSqlQuery>b__13()\r\n   в System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()\r\n   в System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)\r\n   в System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)\r\n   в GTIApp.Models.DbPersonnel.EmployeeFilter(String myFilter)"   string

请帮忙!

Answer 1

SqlQuery 需要一个值 数组，而不是 SqlParameter 对象。这些值中的每一个都将包装在一个 SqlParameter 中。 Entity Framework Raw SQL Queries 中的示例显示如何传递整数值:

using (var context = new BloggingContext()) 
{ 
    var blogId = 1; 

    var blogs = context.Blogs.SqlQuery("dbo.GetBlogById @p0", blogId).Single(); 
}

更新

正如 Evk 所指出的，您可以使用 SqlParameter 对象来传递命名参数。我没有注意到这一点，因为文档格式严重损坏。从单段备注部分提取文字:

Alternatively, you can also construct a DbParameter and supply it to SqlQuery. This allows you to use named parameters in the SQL query string:

context.Database.SqlQuery(typeof(Post), 
                  "SELECT * FROM dbo.Posts WHERE Author = @author", 
                  new SqlParameter("@author", userSuppliedAuthor));

在这种情况下参数名称必须匹配