Looking for a JavaEE Architect or Potential Tech Co-Founder?
Please don't hesitate to contact me.

How to solve multiple active datareader session in mvc3 C#

This problem commonly happens when you try to execute a query with a return type of IEnumerable then execute a second query with the result.

Example, we have a table Animes:

public IEnumerable GetAnimes() {
  return context.Animes;
}

//on the second part of the code we invoke GetAnimes and perform a second sql query;
var animes = GetAnimes(); //line 1
foreach(var anime in animes) { //line 2
  context.Animes.Remove(context.Animes.FirstOrDefault(p=>p == anime)); //line 3
}

The above code will certainly fail. And might throw any of the ff errors:
1.) There is already an open DataReader associated with this Command which must be closed first.
2.) New transaction is not allowed because there are other threads running in the session
Why does this happen? It's because of deferred sql execution. Heard about that? If you think line 1 has already execute its query against the database you're wrong, it just prefer the query for execution. So when you try to execute line 3 it fails, because line 1 is still active.
To resolve this issue, make sure to close the first session before executing the next, by simply adding .ToList() call to line 2.
Also you might want to add MultipleActiveResultSets=True to the connectionString setting.
How to solve multiple active datareader session in mvc3 C# How to solve multiple active datareader session in mvc3 C# Reviewed by Edward Legaspi on Sunday, May 06, 2012 Rating: 5

No comments:

Powered by Blogger.