Resolving the Correct MySql.Data DLL Version in Visual Studio

When you first work with MySQL, many of you would probably choose to install the MySQL Community Server. With the latest version 5.6.12, the installer comes packed with features, such as MySQL Connectors, Workbench, MySQL for Excel, MySQL for Visual Studio and MySQL Server. One package to rule them all!

After installation and database setup, you need to add the reference, MySql.Data.dll, in order for your application to connect to MySQL database. In case you are working with Entity Framework, you will also need to add MySql.Data.Entity.dll to your project. You fire up Visual Studio, right-click Add References, select Extensions under Assemblies and type "MySql" in the search box on the top right:

As depicted above, there are several choices for MySql.Data and MySql.Data.Entity. So the question here is which one do you pick? How about picking the latest dll version (6.7.4 version for .NET 4.0 with the path: "C:\Program Files (x86)\MySQL\Connector NET 6.7.4\Assemblies\v4.0\MySql.Data.dll"), the newer the better right?

You may be surprised to find the error below:

You just installed the latest MySQL .NET Connector 6.7.4, added a reference to it, and did not have any reference to version 6.6.5 in your project. So what went wrong? To find the root cause of this problem, you need to dig deep into the machine.config, located in:


x86 - C:\Windows\Microsoft.NET\Framework\[Version]\Config\machine.config

x64 - C:\Windows\Microsoft.NET\Framework64\[Version]\Config\machine.config

When you first install MySQL Server, it comes with MySQL for Visual Studio and registers the the dll v6.6.5.0 in machine.config. Now, to resolve the problem, there are two options to fix it:

1. Go back to Visual Studio and right-click Add References again, but this time, look for the dll version 6.6.5 in the list with the path: "C:\Program Files (x86)\MySQL\MySQL for Visual Studio 1.0.2\Assemblies\v4.0\MySql.Data.dll".
2. In case you want to use the latest version 6.7.4, you can always edit the reference in machine.config. (Disclaimer: Be really careful when editing machine.config as it can ruin your application configurations on your machine! Always make a backup of the file before making any changes!)

If you like this post, please +1 and share it. Also, feel free to leave a comment down below for any questions.

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!