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>

Answer 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