Entity Framework enumerating SqlQuery result

Question

I have strange error while I am trying to view results of SqlQuery:

var sql = "SELECT @someParam";
var someParamSqlParameter = new SqlParameter("someParam", "Some Value");
var result = _dbContext.SqlQuery<string>(sql, someParamSqlParameter);
var containsAnyElements = result.Any();

So when debugger is at last line and when I try to expand Results View of result it shows me expected result("Some Value") but on invoking last line I got an exception

"The SqlParameter is already contained by another SqlParameterCollection.".

It looks like when I try to open Result View of result it invokes this query again. If that behavior correct? If yes, please explain why that happens.

Answer 1

It looks like when I try to open Result View of result it invokes this query again

You're quite right - you're seeing the effects of Deferred Execution

Database.SqlQuery<T> returns an IEnumerable<T> which is actually an object of type:

System.Data.Entity.Internal.InternalSqlQuery<T>

So your result object is actually just a description of the query - not the query results.

The SQL query is only actually executed on the database when you try to view the results of the query.

What you're seeing is this happening twice: once when your code calls .Any(), and once when the debugger enumerates the result set.

---

You can fix this by explicitly telling EF when to run the query with .ToList():

var result = _dbContext.SqlQuery<string>(sql, someParamSqlParameter).ToList();

The type of result is now List<string> and it contains the results of your query.