Entity Framework open datareader

Question

I'm starting out with Entity Framewrok and I wrote the following simple sample code in a console application:

var post1 = new Post
{
            PostTitle = "Post Title 1",
            PostBody = "post Body 1",
            Comments = new List<Comment>
            {
                new Comment{CommentAuthor="aravind",CommentBody="my first comment on post 1"},
                new Comment{CommentAuthor="dimpu",CommentBody="my second Commment on post1"}
            }
};

using (BlogEntites be = new BlogEntites())
{
    foreach(Post post in be.Posts)
    {
        Console.WriteLine(post.PostTitle);

        foreach (Comment comment in post.Comments)
        {
            Console.WriteLine("\t" + comment.CommentBody);
        }
    } 

    be.Posts.Add(post1);   // post1 added in memory
    be.SaveChanges();      // post1 added to DB
}

Console.WriteLine("press any key to continue...");
Console.ReadKey();

At first, I only had foreach only on the posts and it worked fins and I had an output of all my posts.

Then I wanted to add the comments for each post so I've added another inner foreach - which if I was working with regular object (which to my understanding what ORM should be all about) that what I would have done....

But when running this code I'm getting inner exception:

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

What am I not understanding here?

Thanks

possible duplicate of [There is already an open DataReader associated with this Command which must be closed first](http://stackoverflow.com/questions/6062192/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-c) — Erik Funkenbusch, Jul 31 '14 at 06:06

score 1 · Answer 1 · answered Jul 31 '14 at 06:06

1

What you are not understanding is that you have two foreach statements, each of them is attempting to execute a query from the same data reader. The problem is that you already have an open data read with your first foreach.

There are two ways to solve this. You can either Execute the query into a collection, such as a List by calling foreach(Post post in be.Posts.ToList()) or you can define your connection string with MultipleActiveRecordSets=True (aka MARS support).

answered Jul 31 '14 at 06:06

Erik Funkenbusch

92,674
28
195
291

Hi, I'm guessing MultipleActiveRecordSets=True is not enabled by default for a reason - is there any downside to using it? Also, if I execute against a collection doesn't this make the SQL to query all data? what if I would like to limit that data - say I make 10 blog posts per page and don't want to load all pages and comments in advanced? Thanks – developer82 Jul 31 '14 at 07:02
1

@developer82 - MARS uses more memory, but more importantly, it's a compatibility behavior thing.. they added MARS support later, and defaulted it to off so that the default behavior did not change for people using legacy code. If you only want 10 then you would say `be.Posts.Take(10).ToList()`. Converting to list doesn't change your SQL, it just executes the entire query and puts the objects in memory, rather than retrieving only one row at a time. – Erik Funkenbusch Jul 31 '14 at 07:11

Entity Framework open datareader

1 Answers1