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

Question

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!

Post the full exception, including the call stack. This will show where the exception was actually raised. Use Exception.ToString() — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 24, 2016 at 9:17
everything was wery simple... class EmployeeView has the property int? AgeCount, and my procedure has column Age, not AgeCount... THANKS!!! — sophie
– sophie, Commented Nov 24, 2016 at 9:25

Panagiotis Kanavos · Accepted Answer · 2016-11-24 09:32:05Z

1

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

edited Nov 24, 2016 at 9:32

answered Nov 24, 2016 at 9:21

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Evk Over a year ago

That's not quite true. You can pass SqlParameters there too.

Panagiotis Kanavos Over a year ago

Yes, I just noticed this in the rather ... jumbled documentation

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related