Entity Framework: 'The SqlParameter is already contained by another SqlParameterCollection'

Question

I am trying to execute below code. My goal is to check whether any user exists with the given email id or not.

var result = userDbContext.users.SqlQuery("SELECT * FROM USERS WHERE @email='@emailValue'",
new SqlParameter("@email", "email"),
new SqlParameter("@emailValue","abc@mail.com"));
//new SqlParameter("p1", existingUser.password));

if (result.Count() == 0) //getting exception here
{
    ViewBag.comment = "Sorry. We can not find your credentials";
    return View();
}

But I am getting exception at result.count() and don't know what is going wrong.

Exception is:

"The SqlParameter is already contained by another SqlParameterCollection"

How can I solve this?

You can't parameter your column names, you can parameter your values. — Soner Gönül, Apr 18 '14 at 11:35

score 6 · Answer 1 · answered Sep 04 '19 at 08:22

6

You just need to add ToList() method after the Sql query and remove @ in SqlParameter:

var result = userDbContext.users.SqlQuery("SELECT * FROM USERS WHERE 
@email=@emailValue",
new SqlParameter("email", "email"),
new SqlParameter("emailValue","abc@mail.com")).ToList();
//new SqlParameter("p1", existingUser.password));

if (result.Count() == 0) //getting exception here
{
    ViewBag.comment = "Sorry. We can not find your credentials";
    return View();
}

It will work.

answered Sep 04 '19 at 08:22

Võ Đại Thắng

242
3
10

1

This is the most likely answer to others with the same problem. I was calling SqlQuery with a stored proc and just one parameter. Kept getting the same error and spent ages trying to resolve it. Added ToList to the end of my statement and voila - problem fixed. – PoorbandTony Jan 08 '20 at 09:31

score 3 · Answer 2 · edited Oct 26 '15 at 00:42

3

When you are using params by query, you can't use them by another query. In your code you are using them twice

1- userDbContext.users.SqlQuery....
2- result.Count().

but if you use this code:

"userDbContext.users.SqlQuery(...).Count()"

your Code will be Correct

** SqlQuery does not return a query result until you use a linq extension like any(), tolist()..... on the other hand when you use SqlQuery, the result is an IEnumerable when you use any(), tolist(), first() it's converted to a result

edited Oct 26 '15 at 00:42

Theresa

3,515
10
42
47

answered Oct 25 '15 at 21:20

amin

561
6
18

1

@Theresa: Thank u theresa. but can u tell me why my answer received negative point.? – amin Oct 26 '15 at 07:45

Ehsan Sajjad · Answer 3 · 2014-04-18T11:51:56.543

do something like this:

SqlParameter parameter = new SqlParameter("email", SqlDbType.VarChar);
parameter.Value = "test@yahoo.com";

or try like this:

var check =userDbContext.Database
           .SqlQuery<user>("SELECT * FROM USERS 
                           WHERE email=@emailValue", 
                           new SqlParameter("@emailValue","abc@mail.com")).ToList();

SqlParameter is like this:

var p = new SqlParameter {
    ParameterName = "paramName",
    DbType = DbType.Bit,
    Direction = ParameterDirection.Output
};

score 0 · Answer 4 · answered Sep 03 '20 at 14:07

Sticking .ToList() on the end and just in this place makes the query execute. Any subsequent calls to the original query before the ToList will throw this error, but if you use the ToList result then it will work fine.

I had the same problem as I used an Any() which ran the query and then after I ran First() against the query without the ToList. It tries to run the query again but it has already run and hence has those parameters already defined. So in summary slap ToList on the end and use that result object as you dont want to execute the SQL query again.

score -3 · Accepted Answer · answered Apr 18 '14 at 11:46

-3

You can try this

var check = (from item in userDbContext.users where item.email == email select item).FirstOrDefault();
if (check == null)
{
    ViewBag.comment = "Sorry. We can not find your credentials";
    return View();
}

answered Apr 18 '14 at 11:46

Nadeem Shaikh

362
5
23

Entity Framework: 'The SqlParameter is already contained by another SqlParameterCollection'

5 Answers5