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

Viewed 18467

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}
4 Answers
Related