Integrating MySQL With Visual Studio Using Entity Framework

The article describes the way to establish the proper environment in which one can integrate MySQL with Visual Studio using Entity Framework.

When one is playing with the tools offered by Microsoft and Oracle simultaneously, the two don't always sit right with each other. Quite recently I had to integrate MySQL with Visual Studio using Entity Framework and it was quite a mess.

 

As far as my research on the internet goes, most of the people who tried to achieve the said target encountered the following problems or errors and I was unlucky enough to encounter all of them,

1. Authentication Problem - "Authentication method 'caching_sha2_password' not supported by any of the available plugins"
2. Entity Framework doesn't generate the Entity Data Model,
   1. Choose 'EF Designer from database'
   2. Choose the connection from the drop-down (localhost) that already tested successfully connecting to MySQL database
   3. The "Save connection settings in webc.config as" option is checked
   4. I click 'Next' AND the window disappeared and I get back to the code window
   5. No .edmx file is generated
3. Edmx file is generated but there is no Entity Data Model in it and the output windows show one of the following 2 exceptions: "Illegal mix of collations (utf8_bin, NONE) and (utf8_bin, NONE) for operation '='" OR "Illegal mix of collations for operations 'Union'"
   
4. Entity Framework version is not compatible with the one required by MySQL.

MySQL Integration with Visual Studio Guidelines,

Requirements

• Visual Studio 2012 or above
• Dot Net Framework 4.5 or above
• MySQL Installer Community 5.7.23.0
• MySQL Workbench Community 6.3.9-winx64
• MySQL Connector\Net 6.8.8
• MySQL For Visual Studio 1.2.7
• Entity Framework 5

Installation Steps

• Install the target version of Visual Studio first i.e. VS-2012 or above. The order of installation does matter.
• Download the above mentioned MySQL’s components separately.
• Install the MySQL Installer Community 5.7.23.0 and let it install all the default components. Select "Sha256_Password" authentication instead of "Caching_Sha2_password".
• After its finished installing, close the MySQL installer and reopen it from the start menu.
• Click on the “remove” option.
• Uninstall MySQL for Visual Studio, MySQL Connector\Net, and MySQL Workbench.
• Run the setups for MySQL Connector\Net 6.8.8, MySQL for Visual Studio 1.2.7, & MySQL Workbench Community 6.3.9-winx64 individually and install them manually one by one.
• Now the MySQL environment is all set to be integrated with Visual Studio and Entity Framework.

Note
The versions of the MySQL Components plus the environment of Visual Studio and its frameworks should exactly match with the ones mentioned above, otherwise the things will not be cohesive and the developer will end up dealing with some unwanted and rigid problems.