EDIT: Why DataReader opened error occurs in this map?

Question

I'm trying to pass a map to a view. Before that I populate it with data from some entities(Session, Movie, Theater). I start doing a selection in my sessions:

var sessions = from s in db.Sessions where s.TheaterID == id orderby s.MovieID, s.Time select s;

This is the method. Inside it I iterate over each session and verify if in my map I have a list with the movie name as a key. When execution reaches thie very point I'm receiving the infamous "There is already an open DataReader associated with this Command which must be closed first." exception. I can't cast to a list because of types:

public ActionResult MyAction(int id)
        {
            Theather theater = db.Theater.Find(id);
            ViewBag.TheaterName = theater.NomeCinema;
            var sessions = from s in db.Sessions where s.TheaterID == id orderby s.MovieID, s.Time select s;

            var mapMovies = new Dictionary<string, List<DateTime>>();

            foreach (Session s in sessions)
            {
                if ( !mapMovies.ContainsKey( s.Movie.MovieName ) )
                {
                    mapMovies.Add( s.Movie.MovieName, new List<DateTime>() );
                }
                mapMovies[s.Movie.MovieName].Add(s.Time);
            }
            ViewBag.MapMovies = mapMovies;
            return View();
        }

The error occurs in this line:

if ( !mapMovies.ContainsKey( s.Movie.MovieName ) )

What do I need to do to pass this error?

In which line you get the exception? – Gusman Jul 28 '17 at 02:47 — Gusman, Jul 28 '17 at 02:47
@Gusman Sorry, I forgot to put this. Inserting now. – learner Jul 28 '17 at 02:51 — learner, Jul 28 '17 at 02:51

score 1 · Accepted Answer · answered Jul 28 '17 at 02:57

You are using lazy load, so when you execute if ( !mapMovies.ContainsKey( s.Movie.MovieName ) ) the context tries to issue another query to retrieve the movie, but as you are in the middle of an enumeration (which holds a datareader open) you get the exception.

There are two solutions:

1-Materialize the query converting the result to a list:

var sessions = (from s in db.Sessions 
    where s.TheaterID == id 
    orderby s.MovieID, s.Time 
    select s).ToList();

2-Include the Movie entity on the query so it does eager load instead of lazy load:

var sessions = from s in db.Sessions.Include(s => s.Movie)
    where s.TheaterID == id 
    orderby s.MovieID, s.Time 
    select s;

EDIT: Why DataReader opened error occurs in this map?

1 Answers1