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.

Nick Butler · Accepted Answer · 2013-04-02 10:26:29Z

35

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.

answered Apr 2, 2013 at 10:26

Nick Butler

24.5k4 gold badges51 silver badges72 bronze badges

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

4 Comments

Vladimirs Over a year ago

Great explanation. Thank you very much for so fast and clear answer.

alessalessio Over a year ago

Great mate. I was going crazy, and all the other answers with the same topic were not of any help.

Jonathan Wood Over a year ago

I can't for the life of me figure out how this issue produced the error message that was reported.

Fred Over a year ago

Seriously... I just spent 6 hours... and the answer is to go .ToList()?? Wonderful!

Collectives™ on Stack Overflow

Entity Framework enumerating SqlQuery result

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related