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","[email protected]"));
//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
– Soner Gönül, Commented Apr 18, 2014 at 11:35

Võ Đại Thắng · Accepted Answer · 2019-09-04 08:22:21Z

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","[email protected]")).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 4, 2019 at 8:22

Võ Đại Thắng

2724 silver badges10 bronze badges

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

1 Comment

PoorbandTony Over a year ago

This is the most likely answer to others with the same problem. I was calling SqlQuery<T> with a stored proc and just one parameter. Kept getting the same error and spent ages trying to resolve it. Added ToList<T> to the end of my statement and voila - problem fixed.

Theresa · Accepted Answer · 2015-10-26 00:42:15Z

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, 2015 at 0:42

Theresa

3,65510 gold badges45 silver badges47 bronze badges

answered Oct 25, 2015 at 21:20

amin

5816 silver badges19 bronze badges

1 Comment

amin Over a year ago

@Theresa: Thank u theresa. but can u tell me why my answer received negative point.?

Ehsan Sajjad · Accepted Answer · 2014-04-18 11:51:56Z

2

do something like this:

SqlParameter parameter = new SqlParameter("email", SqlDbType.VarChar);
parameter.Value = "[email protected]";

or try like this:

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

SqlParameter is like this:

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

edited Apr 18, 2014 at 11:51

answered Apr 18, 2014 at 11:46

Ehsan Sajjad

62.6k16 gold badges112 silver badges171 bronze badges

Comments

Andrew · Accepted Answer · 2020-09-03 14:07:18Z

0

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.

answered Sep 3, 2020 at 14:07

Andrew

2,9893 gold badges37 silver badges78 bronze badges

Comments

Alexander · Accepted Answer · 2024-04-10 14:15:56Z

0

I know it's a little late, but better late than never ;-)

We fixed that issue by using the parameters like this:

// Use @par0, @par1 etc. to specify sql parameters in the SQL query/command.
// Just add the values as the following method parameters.
var result = userDbContext.users.SqlQuery("SELECT * FROM USERS WHERE EmailColumn=@par0", "[email protected]");

answered Apr 10, 2024 at 14:15

Alexander

6145 silver badges13 bronze badges

Comments

Nadeem Shaikh · Accepted Answer · 2014-04-18 11:46:22Z

-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, 2014 at 11:46

Nadeem Shaikh

3626 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

6 Answers 6

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related