edmx error 'Unable to cast object of type 'MySql.Data.MySqlClient.MySqlProviderServices' 'System.Data.Common.DBProviderServices'

Question

Getting edmx error Unable to cast object of type 'MySql.Data.MySqlClient.MySqlProviderServices' to type 'System.Data.Common.DBProviderServices' when trying to open edmx designer for MySQL model. I have both Entity Framework 6 for SQL service in the project as well as references to MySQL.

Here is what I have in app.config

<providers>
<provider invariantName="MySql.Data.MySqlClient"
type="MySql.Data.MySqlClient.MySqlProviderServices, 
MySql.Data.Entity.EF6"/>
<provider invariantName="System.Data.SqlClient"
type="System.Data.Entity.SqlServer.SqlProviderServices, 
EntityFramework.SqlServer" />
</providers>
<system.data>
<system.data>
<DbProviderFactories>
<remove invariant="MySql.Data.MySqlClient" />
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" 
description=".Net Framework Data Provider for MySQL"
type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, 
Version=8.0.13.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>

Answer 1

For those of you upgrading to VS2019 and installing MySQL for Visual Studio 1.2.9, you need to edit the following line in your .edmx file (right click the .edmx file inside VS, select Open With... then pick XML (Text) Editor):

        <DesignerProperty Name="UseLegacyProvider" Value="true" />

To the following:

        <DesignerProperty Name="UseLegacyProvider" Value="false" />

I started to have that exact same error message when I tried to open an .edmx file after upgrading so I created a new one from scratch then ran a diff to see what changed.

Answer 2

I've got the solution, the problem was the incompatibility between the .net connector's version and the dll's version.

In my case the project was using MySql.Data dll's version 6.9 but the connector's version was 8.0. So I've replaced the .net connector with the right one (same version).

Answer 3

I found the best solution for this.

1. I change target .NET Framework to 4.5.2 (in Project Properties)

2. Change MySQL.Data library to v6.10.8 (NuGet)