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

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();
                            }
                         }
                     }
            }
 }

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

I count a error as you count, but I had enable Multiple Active Result Sets (MARS),finally I just change IDateReader variable Method from close() to dispose() ,then the problems disappear

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0