Entity Framework 6 with MySQL

Question

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>

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, Feb 25 '15 at 20:04
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, Feb 25 '15 at 20:10
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, Feb 25 '15 at 20:12

score 1 · Answer 1 · answered Feb 25 '15 at 20:05

1

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>

answered Feb 25 '15 at 20:05

Travis J

81,153
41
202
273

I added the second invariantName child node to Providers, and to be safe I also added a reference to MySql.Data.Entity.EF6. However, I'm still getting the first error (an error occured accessing.....). – yesman Feb 25 '15 at 20:18
@Bas - Are you using `MySql.Data.MySqlClient` as the provider name in your connection string? – Travis J Feb 25 '15 at 20:29
Thanks for the help! I added my current web.config to my answer. – yesman Feb 25 '15 at 20:36
@Bas - Unfortunately you added it verbatim! :( I flagged it to be removed from the edit history, but I think you may want to change the password you are using at least. – Travis J Feb 25 '15 at 20:36
Thanks, I changed the login credentials. – yesman Feb 25 '15 at 20:41
@Bas - EF expects the DbContext to share the same name as the connection string. Do you have `public class DefaultConnection : DbContext`? – Travis J Feb 25 '15 at 20:42
The tutorial I'm following has this: `public ApplicationDbContext() : base("DefaultConnection", throwIfV1Schema: false)` – yesman Feb 25 '15 at 20:47
Check out [this](http://stackoverflow.com/questions/26189699/asp-net-mvc4-configuration-error-after-installing-mysql-connector-net/26237830#26237830) too. – Ognyan Dimitrov Feb 26 '15 at 15:25

score 0 · Answer 2 · answered Feb 25 '15 at 22:55

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.

Entity Framework 6 with MySQL

2 Answers2