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

Question

I'm struggling with the error above. I found differente answers here (stack overflow), but none of them solve my problem related with the error.

I just enable MARS in my ConnectionString but without success.

I have a class Product

public class Product
{
    public Product()
    {
        this.Additives = new HashSet<Additive>();
    }

    public int Id { get; set; }
    public string Name { get; set; } // refrigerante
    public string CommercialName { get; set; } // nome popular, ex: fanta laranja
    public string Brand { get; set; } // marca, ex: Coca-cola
    public string Details { get; set; } // composicao, ingredientes
    public HalalState HalalState { get; set; } // estado: halal, haram ou desconhecido
    public DateTime? LastUpdate { get; set; } // date e hora do registo

    public virtual ICollection<Additive> Additives { get; set; } // aditivos
    public int ProviderID { get; set; }
}

}

and class Additive representing additives in products

using System;
using System.Collections.Generic;

namespace Teknowhow.EatHalal.Core.Models
{
    public class Additive
    {
        public Additive()
        {
            this.Products = new HashSet<Product>();
        }

        public int Id { get; set; }
        public string Key { get; set; } // codigo ex: E130
        public string NamePT { get; set; } // nome ex: Acido ascorbico (Vitamina C) em portugues
        public string NameEN { get; set; } // nome ex: Acido ascorbico (Vitamina C) em inglês
        public string Details { get; set; } // detalhes sobre o aditivo, incluindo.
        public HalalState HalalState; // estado: halal, haram ou desconhecido
        public DateTime? LastUpdate { get; set; } // date e hora do registo

        public virtual ICollection<Product> Products { get; set;}
    }
}

I'm writing a code to implement a method on ProductRepository in order to get products with specific additive.

public ICollection<Product> GetProductsByAdditive(string key)
{
    var products = context.Products;
    var productsAdditives = new List<Product>();

     foreach (var p in products)
     {
         var additives = p.Additives;

         foreach (var a in additives)
         {
             if (a.Key.Equals(key))
                productsAdditives.Add(p);   

         }
    }

    return productsAdditives.ToList();
    //TODO: um Metodo úinico que permite pesquisa por nome em PT e EN e codigo 

}

The error occurs exactly after the first foreach loop, on this statement:

var additives = p.Additives;

PS: I'm using EF 6. I'm stacked! Please help!

Heres my Connections

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
        <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    </configSections>
    <connectionStrings>
    <add name="EatHalal" 
         providerName="System.Data.SqlClient" 
         connectionString="Server=192.168.1.150;Database=EatHalal;User Id=user;Password=password;MultipleActiveResultSets=true;"
             />
  </connectionStrings>
</configuration>

Solution 1

context.Products.Where(x=>x.Additives.Any(y=>y.Key==key)).ToList();

your code fails because you are lazy-loading the additives while itterating over the products...

but the itteration itself is nonsense ... if you want to search for something in the database, let the database do its job by giving it something to search for, instead of telling it to give you everything and then sorting out what you want...

imagine if there would be a few hundred million products in your table ...

you would load ALL products and go through them for ages...

look at LINQ

look at .Where()

look at .Select()

get familiar with lambda expressions

Solution 2

You can try as shown below.

Note : You have to bring the data first and then do the mapping and you will not have this issue.

Reason : When you iterate through result of the query (IQueryable) and you will trigger lazy loading for loaded entity inside the iteration.In other words there are multiple data retrieval commands executed on single connection.

         foreach (var p in products.ToList())
            {
                var additives = p.Additives.ToList();

                foreach (var a in additives)
                {
                    if (a.Key.Equals(key))
                    {
                        productsAdditives.Add(p);   
                    }   
                }
            }

Solution 3

To be precise, iterating through an IQueryable uses an open datareader (so you can stop iterating without reading all the products from the database). If you lazy load Additives, EF uses the same connection to retrieve additives so the ADO error.

In this cases, you can read all products using a ToList like suggested in another answer or use Include(p => p.Additives) to eager load data.

In your specific case the best thing is to filter products by additives so your function should be

public ICollection<Product> GetProductsByAdditive(string key)
{
    return context.Products.Where(p => p.Additives.Select(a => a.Key).Contains(key)).ToList();
}

Solution 4

You should be able to fix this by eagerly loading Additives at the same time you load Products, instead of relying on lazy loading.

var products = context.Products.Include(p => p.Additives);

This will fetch Additives as part of the same query that loads Products. It will also be more efficient, as it will only load Additives that are linked to the Products you return. Though you are loading ALL Products at the moment, so it may not be a huge improvement for this exact query.

View more solutions

Comments

[elmodai]

Thank you @Sampath. It works. Can you explain me why it works?

[elmodai]

I've tried this code but key is a string, while Contains receive a Additive object as parameter, then I get a compile error. But anyway, I'll improve my understand of lambda expression. I appreciate your approach. Thanks!

[DarkSquirrel42]

ah... i overlooked the Key Property on Additives... see edit

[elmodai]

Yes. Lazy loading causes this. Because I have virtual properties.

[elmodai]

Oh! Ok. Nice suggestion. I'll switch the loading approach. Surly, eager loading is more suitable for this purpose. Thank you!

[elmodai]

Explanation that make sense. Thank you! I have adopted this approach.