Entity Framework runtime error - There is already an open DataReader associated with this Command which must be closed first

Question

I have a sample application with the following models and context-

   public class Department
   {
     public int Id { get; set; }
     public string Name { get; set;}
     public virtual ICollection<Student> Students { get; set; }
   }

   public class Student
   {
     public int Id { get; set; }

     public string Name { get; set; }

     public virtual Department Department { get; set; }
   }
 public class TestContext : DbContext
{
    public DbSet<Student> Students { get; set; }
    public DbSet<Department> Departments { get; set; }
}

Below is the progarm.cs code. When i debug and the debugger reach - Consol.WriteLine method inside foreach block, i get the error- There is already an open DataReader associated with this Command which must be closed first. Why is that. Should not context close automatically once it opens connection when the foreach code is reached.

    class Program
{
    static void Main(string[] args)
    {
        using (var context = new TestContext())
        {
            var students = context.Students.Where(s => s.Id == 1);

            foreach (var student in students)
            {
                Console.WriteLine("Student : {0} - Department {1}", student.Name, student.Department.Name);
            }

            Console.ReadLine(); 
        }
    }

Does this answer your question? [There is already an open DataReader associated with this Command which must be closed first](https://stackoverflow.com/questions/6062192/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-c) — marsze, Jun 16 '20 at 09:50

Arman Ebrahimpour · Answer 1 · 2020-06-10T08:08:59.570

1

This issue raises due to this in foreach loop: student.Department.Name.

In fact here you have one DataReader associated with foreach loop and one associated with loading Department

Change retrieval this way and your problem should be solved:

var students = context.Students.Where(s => s.Id == 1).Include(s => s.Department).ToList();

edited Jun 10 '20 at 08:08

answered Jun 10 '20 at 07:44

Arman Ebrahimpour

4,252
1
14
46

With the added include, you might not need to call `ToList` explicitly – ESG Jul 03 '20 at 20:22

score 1 · Accepted Answer · edited Jul 03 '20 at 20:46

To answer your question, you have your TestContext wrapped in a using statement which means that the TestContext will remain open until the end of the using statement.

Your initial call is essentially a query which only gets executed when your foreach loop is hit:

var students = context.Students.Where(s => s.Id == 1);

This is causing the error. I.e., it is resulting in multiple queries on a DataReader that does not allow it.

@Arman is correct: .ToList() will solve your problem because the query is being executed to get all the students to a list before the foreach loop. Obviously, there are a number of solutions to this problem. Probably the most common one would be set MARS (Multiple Active Result Sets) to true on your connection string; i.e.:

Server={myServer};Initial Context={myDb};MultipleActiveResultSets=True;…

This will allow multiple open DataReaders.

Entity Framework runtime error - There is already an open DataReader associated with this Command which must be closed first

2 Answers2