There is already an open DataReader associated with this Connection which must be closed first + asp.net mvc

Question

I have a mysql database with a table entites with multiple fields in it like entity_title, entity_description, ... . In the table there are also 3 foreign keys user_id, region_id an category_id.

In my Index View I would like to show all the entities in a table (show the title, description, ... , the user name, the region name and the category name).

This is what I do in my Controller:

public ActionResult Index()
{
    var model = this.UnitOfWork.EntityRepository.Get();
    return View(model);
}

In my Repository I do this:

public virtual IEnumerable<TEntity> Get(
    Expression<Func<TEntity, bool>> filter = null,
    Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
    string includeProperties = "")
{
    IQueryable<TEntity> query = _dbSet;

    if (filter != null)
    {
        query = query.Where(filter);
    }

    foreach (var includeProperty in includeProperties.Split
            (new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
    {
        query = query.Include(includeProperty);
    }

    if (orderBy != null)
    {
        return orderBy(query).ToList();
    }
    else
    {
        return query.ToList();
    }
}

I always get the error Input string was not in a correct format on the last rule (return query.ToList()).

But when I check the _dbSet after the rule IQueryable<TEntity> query = _dbSet; it already gives the error: There is already an open DataReader associated with this Connection which must be closed first.

This probably comes because I want to select from more then one table. But how can I fix this? I tried adding MultipleActiveResultSets=True" to my ConnectionString like this:

<connectionStrings>
<add name="reuzzeCS" connectionString="server=localhost;uid=root;pwd=*****;Persist Security Info=True;database=reuzze;MultipleActiveResultSets=True"" providerName="MySql.Data.MySqlClient" />

But that gave me the error that the keyword doesn't exists, because I work with MySql.Data.MySqlClient ..

The Query executed is:

{SELECT Extent1.entity_id, Extent1.entity_title, Extent1.entity_description, Extent1.entity_starttime, Extent1.entity_endtime, Extent1.entity_instantsellingprice, Extent1.entity_shippingprice, Extent1.entity_condition, Extent1.entity_views, Extent1.entity_created, Extent1.entity_modified, Extent1.entity_deleted, Extent1.user_id, Extent1.region_id, Extent1.category_id FROM entities AS Extent1}

But when he wants to execute the query and I want to expand the results, I get the error There is already an open DataReader associated with this Connection which must be closed first

EDIT:
My full repository:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace App.Data.orm.repositories
{
// REPO FROM TEACHER
public class GDMRepository<TEntity> where TEntity : class
{
    internal GDMContext _context;
    internal DbSet<TEntity> _dbSet;

    public GDMRepository(GDMContext context)
    {
        this._context = context;
        this._dbSet = _context.Set<TEntity>();
    }

    public virtual IEnumerable<TEntity> Get(
        Expression<Func<TEntity, bool>> filter = null,
        Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
        string includeProperties = "")
    {
        IQueryable<TEntity> query = _dbSet;

        if (filter != null)
        {
            query = query.Where(filter);
        }

        foreach (var includeProperty in includeProperties.Split
            (new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
        {
            query = query.Include(includeProperty);
        }

        if (orderBy != null)
        {
            return orderBy(query).ToList();
        }
        else
        {
            return query.ToList();
        }
    }

    public virtual TEntity GetByID(object id)
    {
        return _dbSet.Find(id);
    }

    public virtual void Insert(TEntity entity)
    {
        _dbSet.Add(entity);
    }

    public virtual void Delete(object id)
    {
        TEntity entityToDelete = _dbSet.Find(id);
        Delete(entityToDelete);
    }

    public virtual void Delete(TEntity entity)
    {
        if (_context.Entry(entity).State == EntityState.Detached)
        {
            _dbSet.Attach(entity);
        }
        _dbSet.Remove(entity);
    }

    public virtual void Update(TEntity entity)
    {
        _dbSet.Attach(entity);
        _context.Entry(entity).State = EntityState.Modified;
    }
}
}

GDMContext class:

using App.Data.orm.mappings;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace App.Data.orm
{
public class GDMContext:DbContext
{
    public GDMContext() : base("reuzzeCS") { }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        //REMOVE STANDARD MAPPING IN ENTITY FRAMEWORK
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

        //REGISTER MAPPERS
        modelBuilder.Configurations.Add(new UserMapping());
        modelBuilder.Configurations.Add(new PersonMapping());
        modelBuilder.Configurations.Add(new RoleMapping());

        modelBuilder.Configurations.Add(new EntityMapping());
        modelBuilder.Configurations.Add(new MediaMapping());
        modelBuilder.Configurations.Add(new BidMapping()); 
        modelBuilder.Configurations.Add(new CategoryMapping());
        modelBuilder.Configurations.Add(new AddressMapping());
        modelBuilder.Configurations.Add(new RegionMapping()); 
        modelBuilder.Configurations.Add(new MessageMapping());
    }
}
}

My entity Model:

public class Entity
{
    public Int64 Id { get; set; }
    [Required(ErrorMessage = "Title is required")]
    [StringLength(255)]
    [DisplayName("Title")]
    public string Title { get; set; }
    [Required(ErrorMessage = "Description is required")]
    [DisplayName("Description")]
    public string Description { get; set; }
    [Required]
    public DateTime StartTime { get; set; }
    [Required]
    public DateTime EndTime { get; set; }
    /*[Required(ErrorMessage = "Type is required")]
    [StringLength(16)]
    [DisplayName("Type")]
    public string Type { get; set; }*/
    [Required]
    public decimal InstantSellingPrice { get; set; }
    public Nullable<decimal> ShippingPrice { get; set; }

    public Condition? Condition { get; set; }
    public Nullable<Int64> Views { get; set; }

    [Required]
    public DateTime CreateDate { get; set; }
    public Nullable<DateTime> ModifiedDate { get; set; }
    public Nullable<DateTime> DeletedDate { get; set; }

    public Int32 UserId { get; set; }

    public Int32 RegionId { get; set; }

    public Int16 CategoryId { get; set; }

    public virtual User User { get; set; }
    public virtual Region Region { get; set; }
    public virtual Category Category { get; set; }
    //public virtual ICollection<Category> Categories { get; set; }
    public virtual ICollection<User> Favorites { get; set; }
    public virtual ICollection<Bid> Bids { get; set; }
    public virtual ICollection<Media> Media { get; set; }
}

public enum Condition
{
    New = 1,
    Used = 2
}

My Entity Mapping:

internal class EntityMapping : EntityTypeConfiguration<Entity>
{
    public EntityMapping()
        : base()
    {
        this.ToTable("entities", "reuzze");

        this.HasKey(t => t.Id);
        this.Property(t => t.Id).HasColumnName("entity_id").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        this.Property(t => t.Title).HasColumnName("entity_title").IsRequired().HasMaxLength(255);
        this.Property(t => t.Description).HasColumnName("entity_description").IsRequired();
        this.Property(t => t.StartTime).HasColumnName("entity_starttime").IsRequired();
        this.Property(t => t.EndTime).HasColumnName("entity_endtime").IsRequired();
        //this.Property(t => t.Type).HasColumnName("entity_type").IsRequired();
        this.Property(t => t.InstantSellingPrice).HasColumnName("entity_instantsellingprice").IsRequired();
        this.Property(t => t.ShippingPrice).HasColumnName("entity_shippingprice").IsOptional();
        this.Property(t => t.Condition).HasColumnName("entity_condition").IsRequired();
        this.Property(t => t.Views).HasColumnName("entity_views").IsOptional();
        this.Property(t => t.CreateDate).HasColumnName("entity_created").IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);
        this.Property(t => t.ModifiedDate).HasColumnName("entity_modified").IsOptional();
        this.Property(t => t.DeletedDate).HasColumnName("entity_deleted").IsOptional();

        this.Property(t => t.UserId).HasColumnName("user_id").IsRequired();
        this.Property(t => t.RegionId).HasColumnName("region_id").IsRequired();
        this.Property(t => t.CategoryId).HasColumnName("category_id").IsRequired();

        //FOREIGN KEY MAPPINGS
        this.HasRequired(t => t.User).WithMany(p => p.Entities).HasForeignKey(f => f.UserId).WillCascadeOnDelete(false);
        this.HasRequired(t => t.Region).WithMany(p => p.Entities).HasForeignKey(f => f.RegionId);
        this.HasRequired(t => t.Category).WithMany(p => p.Entities).HasForeignKey(f => f.CategoryId);

        //MANY_TO_MANY MAPPINGS
        this.HasMany(t => t.Favorites)
            .WithMany(t => t.Favorites)
            .Map(mc =>
            {
                mc.ToTable("favorites");
                mc.MapLeftKey("entity_id");
                mc.MapRightKey("user_id");
            });
    }
}

Link to stacktrace image!

UPDATE:

• base {SELECT Extent1.entity_id, Extent1.entity_title, Extent1.entity_description, Extent1.entity_starttime, Extent1.entity_endtime, Extent1.entity_instantsellingprice, Extent1.entity_shippingprice, Extent1.entity_condition, Extent1.entity_views, Extent1.entity_created, Extent1.entity_modified, Extent1.entity_deleted, Extent1.user_id, Extent1.region_id, Extent1.category_id FROM entities AS Extent1} System.Data.Entity.Internal.Linq.InternalQuery {System.Data.Entity.Internal.Linq.InternalSet}

Answer 1

Your problem is

I think MySql connector probably doesn't support multiple active result sets and because of that the setting in connection string didn't help you.

So Please try this way instead of your code

Edit :

query.Include("User").Include("Region").Include("Category").ToList(); 

Let me know, if you get same error after this change.

Update:

I have change some thing for you Please use this code instead of your method

 public virtual IEnumerable<TEntity> Get(
    Expression<Func<TEntity, bool>> filter = null,
    Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
    string includeProperties = "")
        {
            IQueryable<TEntity> query = _dbSet;

            if (filter != null)
            {
                query = query.Where(filter);
            }


            if (orderBy != null)
            {
                return orderBy(query.Include("User").Include("Region").Include("Category").ToList()).ToList();
            }
            else
            {
                return query.Include("User").Include("Region").Include("Category").ToList(); 
            }
        }

Update 2:

It is not about closing connection. EF manages connection correctly. My understanding of this problem is that there are multiple data retrieval commands executed on single connection (or single command with multiple selects) while next DataReader is executed before first one has completed the reading. The only way to avoid the exception is to allow multiple nested DataReaders = turn on MultipleActiveResultSets. Another scenario when this always happens is when you iterate through result of the query (IQueryable) and you will trigger lazy loading for loaded entity inside the iteration.

And stack overflow have lot of peoples got the solutions for your question

1: Entity Framework: There is already an open DataReader associated with this Command

2: How to avoid "There is already an open DataReader associated with this Connection which must be closed first." in MySql/net connector?

3: Error: There is already an open DataReader associated with this Command which must be closed first

and my personal advice for, I think you don't spent more time for this error, because waist of time and energy , and you can do it by using by manual query . So please try different ways.

You don't need split and formatting queries for avoiding input string was not correct format error

You can do this way instead of return query.ToList();

return _dbSet.Users
    .Include(x => x.Region)
    .Include(x => x.Category).ToList();

I think you can do it by using my above SO link's.

And My main question is :

Entity Framework can support ORM Concept, So why you don't try this way?. You can change the idea for using ORM Concept. It's may be solve this problem. This is a link for that and please see this tutorial

Answer 2

UPDATE

OK, so from your stack trace it looks like the "open DataReader associated ...blah" was a red-herring. Maybe that was visual studio and its intellisense visual debugger thingy trying to show you the values contained in your dbset but a connection was still open or something like that.

To me, it looks like EF's MySqlDatareader is doing its job of enumerating the results and mapping them to POCO's.

Maybe there is a column that is a varchar(..) or something of that sort on a table in your Database, and on your POCO's its mapped property is oftype(Int32). So if there is a an empty string or a value that isn't a number in the database I believe that an Input string was not in a correct format exception should be expected when you try convert a null or empty string value to an Int. Just tried this now to see:

---

I think the issue is that MySql doesn't support MARS and maybe it also doesn't suport Lazy Loading. While I couldn't find anything official to say this was the case I found a few posts with the same issue as you.

http://www.binaryforge-software.com/wpblog/?p=163

MySQL + Code First + Lazy Load problem !

http://forums.mysql.com/read.php?38,259559,267490

Now up until fairly recently I thought that calling ToList() on an IQueryable would Load the Results into memory and any Navigation properties would not be LazyLoaded, this is not strictly true. While the result will be persisted into Memory any virtual Navigation properties of that result will still be lazy loaded if you try to access them.

On a high level LazyLoading works because entity framework overrides your `virtual' navigation properties and uses its own implementation to load entities from the database.

My guess is that in your View or somewhere else in your code you must be accessing a property that you haven't explicitly loaded using an Include. My guess is that EF may be trying to do this on a single connection and that is why you see:

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

I would turn off Lazyloading by doing the following:

public class GDMContext:DbContext
{
    public GDMContext() : base("reuzzeCS") 
    {
        base.Configuration.LazyLoadingEnabled = false; 
    }
}

Hope this helps.

Answer 3

According to your stack trace, the framework appears to have an issue converting a string to an integer. To quote another SO answer, "EF throws error each time you set a type in the model that is different from the table."

You have a few options.

If you are using a code first approach, I suggest you regenerate your database.

If you are using a "code second" approach (which maps your database tables to POCO classes), then I suggest you regenerate your database.

If you have not had luck with either of the above, you may at least narrow down which column is having the issue by testing each of your integer-based columns like this:

public ActionResult Index()
{
    var model1 = this.UnitOfWork.EntityRepository.Get(
        includeProperties: "category_id");
    // Did that produce an error? If not, try another column:
    var model2 = this.UnitOfWork.EntityRepository.Get(
        includeProperties: "region_id");
    // etc.

    // If you get to your original code, then try testing other columns
    var model = this.UnitOfWork.EntityRepository.Get();
    return View(model);
}

What if the above does not work? There could be an issue with your connection string, as mentioned in this SO answer. This is probably a long-shot, given that your stack trace does not seem to stumble over creating a connection, but it is worth noting in your connection string for MARS there appears to be an extra double quote (though I am sure it is probably just a transcription error.) In any case, if you cannot get any query to work, ensure your connection string looks normal, like the following:

<connectionStrings>
    <add name="reuzzeCS"
        connectionString=
        "server=localhost;database=Reuzze;User Id=root;password=P4ssw0rd"
        providerName="MySql.Data.MySqlClient" />
</connectionStrings>

What if the above does not work? Check that your version of EntityFramework plays nice with your version of MySQL.