CODEWITHSUNDEEP
c#asp.net-mvc-4
Amit
Amit

Reputation: 569

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

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?

Upvotes: 13

Views: 22242

Answers (6)

Alexander
Alexander

Reputation: 614

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

Upvotes: 0

Andrew
Andrew

Reputation: 2939

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.

Upvotes: 0

Võ Đại Thắng
Võ Đại Thắng

Reputation: 272

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.

Upvotes: 6

amin
amin

Reputation: 581

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

Upvotes: 3

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62488

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

Upvotes: 2

Nadeem Shaikh
Nadeem Shaikh

Reputation: 362

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

Upvotes: -3

Related Questions