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?

Answer 1

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