CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6dbcontext
SunilA
SunilA

Reputation: 583

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

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

Upvotes: 1

Views: 173

Answers (2)

Christo Cilliers
Christo Cilliers

Reputation: 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.

Upvotes: 1

Arman Ebrahimpour
Arman Ebrahimpour

Reputation: 4461

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

Upvotes: 1

Related Questions