Mistake. There is already an open DataReader associated with this Command, which should be closed first

Question

I am using sql connection to access various tables in my database. However, the code returns me an error below.

Error: "There is already an open DataReader associated with this command that must be closed first" :

MyContext conn = new MyContext() protected void ChangeName(int id) { User user = conn.MyOtherTable.First(x => x.id == id); var elements = conn.MyTable.Where(x => x.id == id && x.name == name).OrderBy(x => x.id).OrderBy(x => x.name). .Select(t => new { t.id, t.name, }).GroupBy(t => new { t.id, t.name, }); foreach (var item in elements) { foreach (var row in item) { for (int j = 1; j <= 5; j++) { if (row.name == "name") { user.name1 = row.name; conn.SaveChanges(); } if (row.name == "name2") { user.name2 = row.name; conn.SaveChanges(); } } } } } 

Answer 1

LINQ (when talking to the database) is usually an unbuffered buffering API. To do what you want, follow these steps:

• enable multiple active result sets (MARS)
• data buffer first

I prefer the second option; it just includes adding .ToList() to your first line:

 var elements = conn.MyTable.Where(x => x.id == id && x.name == name) .OrderBy(x => x.id).OrderBy(x => x.name). .Select(t => new { t.id, t.name, }).GroupBy(t => new { t.id, t.name, }) .ToList(); 

now that this line has started, we know that we already have all the data in memory, and the reader has closed; previously, he could still talk about entering a line from the reader.

For completeness, the inclusion of MARS is discussed here - this would not be my recommendation.

Answer 2

I am counting the error as you count, but I have included some active result sets (MARS), finally I just change the IDateReader Method variable from the close () method to remove (), then the problems disappear