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

Question

I have the following code that retrieves data from a customer table

var customers= context.CustomerEntities.Include("Addresses").Select(Mapper.Map).ToList();

The mapper function, maps the entity object, to a business object, and it looks like this

    internal static Customer Map(CustomerEntity entity)
    {
        if (entity == null)
            return null;

        return new Customer
        {
            Id = entity.Id,
            Name = entity.Name,
            Addresses = Map(entity.Addresses)

        };
    }

Now, the above code runs well.

However, when I try to do this:

var customers= context.CustomerEntities.Select(Mapper.Map).ToList();

I get the error message: There is already an open DataReader associated with this Command which must be closed first when the Mapper function is being executed.

Now I'm aware that to solve this problem, I have to set multipleactiveresultsets=True in my connection string. I have tried it, and it did solve my problem.

However, when I ran SQL profiler, querying all customers from entity framework automatically retrieved all the addresses as well, even though I didn't need them.

Is there a workaround besides having to set multipleactiveresultsets=True? I don't want the addresses to be lazy loaded all the time.

Answer 1

I believe that is because for each Customer the select statement is causing to go and read the database again. Why don't you do first ToList() and then apply the mapping (Select) something like:

var customers= context.CustomerEntities.ToList().Select(Mapper.Map);

I believe this would bring the data first and then do the mapping and you wouldn't have that issue.