Entity Framework 6 with MySQL

2
votes

I'm trying to get Entity Framework 6 to work with a MySQL database. I am following this guide:

http://bitoftech.net/2015/01/21/asp-net-identity-2-with-asp-net-web-api-2-accounts-management/

Where I'm replacing the local database with a MySQL one. I installed the MySQL.NET Connector/Net Nuget package. However, replacing the connection string with that of my host is giving me problems. I can't get the enable-migrations command to work properly. On their FAQ pages, my host states you need to use this connection string:

Server=myServerAddress;Database=MyDataBase;User=MyUser;Password=myPassword

Which led me to this connection string:

<add name="DefaultConnection" connectionString="Server=12.345.6.789:1234;Database=MyDatabaseName;User=MyUserName;Password=MySuperSecretPassword" providerName="System.Data.SqlClient" />

I set the IP and portnumber they gave me as a server. Note the providername. I get this error when running enable-migrations in the console:

An error occurred accessing the database. This usually means that the connection to the database failed. Check that the connection string is correct and that the appropriate DbContext constructor is being used to specify it or find it in the application's config file. See http://go.microsoft.com/fwlink/?LinkId=386386 for information on DbContext and connections. See the inner exception for details of the failure.

When I switch the providername to MySql.Data.MySqlClient, I get this error:

No Entity Framework provider found for the ADO.NET provider with invariant name 'MySql.Data.MySqlClient'. Make sure the provider is registered in the 'entityFramework' section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

What am I doing wrong? What's the simplest way to set up a connection to my MySQL database using EF6?

Edit: My current web.config: 

<connectionStrings>
<add name="DefaultConnection" connectionString="Server=x.x.x.x;Database=RademaekAuthentication;User=x;Password=x" providerName="MySql.Data.MySqlClient" />
</connectionStrings>

<providers>
  <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
</providers>
2
mysqlasp.netentity-framework
As a brief note on using MySQL with ef. I spent a very long time using the mysql connector for .net. It is fundamentally flawed in many aspects, as is mysql for use with .net. The lack of support in the ISAM mysql database structure for transactions, and its aggressive read locks make it unuseable with concurrent users. I would suggest switching to MSSQL at your earliest convenience and avoid this headache and all of the ones in the future. – Travis J
I would love nothing more than to use the wonderfully integrated MSSQL, I agree with you 100%. However, the host charges extra costs for the privilege of using MSSQL databases. My client does not want to spend this extra money. – yesman
I understand that, but the price is really not that significant because the database size will be really small. You will more than likely never exceed 10mb on a small project. Often you can get a small amount, even up 1 gb, for $10/month which imo is reasonable. Further, the time you waste on MySQL will greatly outweigh the savings of using it. At least impress upon them the value of your time :) – Travis J

2 Answers

1
votes

Inside of your web.config, the entire config is under the tag <configuration>. Anywhere inside of that you need to have

<entityFramework>
 <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
  <parameters>
    <parameter value="v11.0" />
  </parameters>
 </defaultConnectionFactory>
 <providers>
  <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
 </providers>
</entityFramework>
0
votes

I recently had this problem and solved it with the following connection string:

<add name="DefaultMySqlConnection" providerName="MySql.Data.MySqlClient" connectionString="Data Source=localhost; port=3306; Initial Catalog=<name>; uid=<user>; pwd=<password>;" />

The Web.config also contains the following :

<entityFramework>
<defaultConnectionFactory
    type="System.Data.Entity.Infrastructure.SqlConnectionFactory,
          EntityFramework" />
<providers>
<provider
    invariantName="MySql.Data.MySqlClient"
    type="MySql.Data.MySqlClient.MySqlProviderServices,
          MySql.Data.Entity.EF6" />
</providers>

And in the system.data tag(create it right before the </configuration> tag if it doesn't exist), add the following code:

<DbProviderFactories>
  <remove invariant="MySql.Data.MySqlClient" /><add name="MySQL" description="ADO.Net driver for MySQL" invariant="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data"/>
</DbProviderFactories>

This is my DbContext class:

[DbConfigurationType(typeof(MySqlEFConfiguration))]
public class DatabaseContext : DbContext
{
    public DatabaseContext(string connectionString)
        : base(connectionString)
    { }

After this make sure to run the migrations and it hopefully works.

EDIT: Maybe it's worth mentioning I don't have the <providers> part in my config.