sophie sophie - 14 days ago 10
C# Question

SqlParameter is already contained by another SqlParameterCollection (EF, SqlQuery, Stored Procedure call)

I have a method

List<EmployeeView> EmployeeFilter(string myFilter)
in my Entity Framework DbContext. It never gets to the end. When result is executed, in result.RawView.Message is


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;
}


I've tried to rewrite method as following. but nothing helped

1.

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


instead of

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} };


or

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


or

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


or

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


Stored procedure code:

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


Exception catched:

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


Please, help!

Answer

SqlQuery expects an array of values, not SqlParameter objects. Each of these values will be wrapped in a SqlParameter. The example in Entity Framework Raw SQL Queries shows how to pass an integer value:

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

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

Update

As Evk noted, you can use a SqlParameter object to pass a named parameter. I didn't notice this because the documentation format is badly broken. Extracting the text from the single-paragraph remarks section:

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));

The parameter names have to match in this case