Getting MvcScaffolding and Entity Framework 5.0 to work with MySQL Connector

While working on an ASP.NET MVC3 project, I came across an incident where I had to switch the database from MS SQL to MySQL. In this project, I have been extensively using MvcScaffolding along with Entity Framework (EF) 5.0.

The problem I faced was getting the model class that was scaffolded with the "-Repository" option to work in harmony with MySQL Connector. After making all the required changes to bring the compilation to succeed, I opened the page only to see the error, "There is already an open DataReader associated with this Connection which must be closed first." on the line that tried to access the model's associated collection:

// Connection opens, the page queries something.
<td>
    @item.ProductName
</td>
<td>
    @(item.products == null ? "None" : item.products.Count.ToString()) // Connection still opens, the page queries again. BAM! Exception!
</td>

I googled through various threads to find out that:

1. The Entity Framework uses Lazy Loading and 
2. When the collection was called, the server tried to query second data set using the same connection.

In MS SQL, this can be easily rectified by appending "MultipleActiveResultSets=True" to the connection string. However, MySQL does not support Multiple Active Result Sets (MARS). To solve this problem, we need to avoid using the Lazy Loading feature. 

The method, Index(), in the Controller class looks like this:

public ViewResult Index()
{
    return View(productRepository.All);
}

It calls the All property of the IproductRepository interface and its implementation, which look like this:

public interface IproductRepository : IDisposable
{
    IQueryable<product> All { get; }
    IQueryable<product> AllIncluding(params Expression<Func<product, object>>[] includeProperties);
    product Find(string id);
    void InsertOrUpdate(product product);
    void Delete(string id);
    void Save();
}

public class productRepository : IproductRepository
{
    dbEntities context = new dbEntities();

    public IQueryable<product> All
    {
        get { return context.products; }
    }

    public IQueryable<product> AllIncluding(params Expression<Func<product, object>>[] includeProperties)
    {
        IQueryable<product> query = context.products;
        foreach (var includeProperty in includeProperties) {
            query = query.Include(includeProperty);
        }
        return query;
    }
    ...
}

The Lazy Loading feature is actually the default behavior of IQueryable<T> type that would only execute upon being called. In order to make the database connection close after each query, we must call ToList() on the IQueryable<T> collection. To do so, we need to replace IQueryable<T> with IEnumerable<T> in the repository interface:

public interface IproductRepository : IDisposable
{
    IEnumerable<product> All { get; }
    IEnumerable<product> AllIncluding(params Expression<Func<product, object>>[] includeProperties);
    product Find(string id);
    void InsertOrUpdate(product product);
    void Delete(string id);
    void Save();
}

public class productRepository : IproductRepository
{
    dbEntities context = new dbEntities();

    public IEnumerable<product> All
    {
        get { return context.products.ToList(); }
    }

    public IEnumerable<product> AllIncluding(params Expression<Func<product, object>>[] includeProperties)
    {
        IQueryable<product> query = context.products;
        foreach (var includeProperty in includeProperties) {
            query = query.Include(includeProperty);
        }
        return query.ToList();
    }
    ...
}

Hope this little trick saves you some headache and hair-pulling!