Entity Framework causing error “There is already an open DataReader associated with this Command which must be closed first”

So how many people are like me and have code like this and when you step through it, you get what you want back, but when it goes back to the web application you get an error similar to “There is already an open DataReader associated with this Command which must be closed first”

// GET api/values
public IEnumerable<RestPerformance> Get()
{
    IEnumerable<RestPerformance> restList;
    using (sandboxEntities1 context = new sandboxEntities1())
    {

        restList = context.GetAllItems();
    }

    return restList;
}

This is an interesting issue that I thought I would blog about so it might help others from pulling their hair out. If you have done anything with WebAPI or Data Services, you know that things need to be returned in certain ways or are recommended as such. In Data Services, it is required that you always return IQueryable. Well WebAPI defaults to the list Get to be IEnumerable, so one would think this is the most efficient way to return things back since it was defaulted that way.

This situation is a learning experience of two fold.

1.) Assumptions get me in trouble every darn time.

2.) POCO objects are always the best thing to do EVEN in basic testing.

3.) Entity Framework uses the Lazy loading technique.

With all of this listed out, lets take a look here. The reason this fails is because Entity uses a feature of .NET called Lazy Loading. This feature allows you to define something without actually getting all the information until it is actually required. So what does this mean? This means the line that I call the context.GetAllItems is not actually completed until WebAPI is returning back to the browser with the return, which is outside of the using. A connection created with the using keyword calls IDispose as soon as you are outside of the closing bracket. Under normal circumstances, if you had a POCO object and this call was in a repository, you could then convert the returned data into the POCO list without failure because you are still in code and not pushing out to a UI. This is why I always preach in all my blogs and youtube videos the need for custom objects to hide the actualy DAL. I could do another article on the reasons this is important.

So what are your options? Well, you can place the return inside the using so that it is still within the connection, but that’s just bad practice. The best way I found to solve this is to do a ToList and return it as an IList. The to List converts it forcing the loading to occur. The code would look similar to this:

public IEnumerable<RestPerformance> Get()
{
    IList<RestPerformance> restList;
    using (sandboxEntities1 context = new sandboxEntities1())
    {

        restList = context.GetAllItems().ToList();
    }

    return restList;
}

Hope this helps others!!

Happy Coding!!

Advertisements

About Gregg Coleman

I am Senior-level Software Engineer working primarily these days with .NET. I have a good working knowledge of ASP.NET MVC, Web Forms, WCF web services and Windows Services. I spend much of my time in the Web Services (SOAP and REST) world in my current job designing and implementing various SOA architectures. I have been in the software engineering industry for about 6 years now and will not now nor ever consider myself an "expert" in programming because there is always so much to learn. My favorite thing about designing software is there are always new emerging technologies and something to learn every day! My current job has me spending much of my job on the bleeding edge of technologies and changing gears all the time, so I'm never bored and always challenged. On my spare time I enjoy weight training, reading and venturing to new places near by. Of course programing and learning new technologies are another hobby of mine.
This entry was posted in .NET, .NET 4.5, C#, Databases, Entity Framework, LINQ, LINQ To SQL, Programming and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s