Error: There is already an open DataReader associated with this Command which must be closed first

asp.net, c#, sql

Solution

LINQ (when talking to a database) is usually a non-buffered spooling API. To do what you want, either:

- enable Multiple Active Result Sets (MARS)

- buffer the data first

I prefer the second option; it just involves 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 after this line has run we know we already have all the data in memory and the reader has closed; previously it could still have been talking row input from the reader.

For completeness, enabling MARS is discussed here - it wouldn't be my recommendation, though.

Problem

I'm using a sql connection to access to different tables in my database. However the code returns me the below error. Error : "There is already an open DataReader associated with this Command which 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(); } } } } } ```

Original source