EF6 and MySQL Generate Database from Model

Question

I've tried every solution I've found on the web with no success. I continually get the error:

Running transformation: System.InvalidOperationException: The SSDL generated by the activity
called 'CsdlToSsdlAndMslActivity' is not valid and has the following errors: 
  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.

I have an empty Console project and a very basic model to test this with. Through the NuGet Package Console I'd executed: Install-Package MySQL.Data.Entities which installed EF6, MySQL.Data and MySQL.Data.Entities and hooked up all the references and copied the relevant DLL's into the bin/Debug folder.

I wired up the provider in the following (entire config file listed)

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <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"></provider>
    </providers>
  </entityFramework>
</configuration>

As far as I can tell I have the provider registered. I have the latest MySQL connector updated as well. Where am I going wrong?

Answer 1

To support full functionality EF Designer requires 3 things:

• EF runtime provider (you seem to have it)
• DDEX provider for your database - DDEX is the way VS abstracts databases and as a result can talk to any database. Note that this is not something that is EF specific but EF Designer relies on it (again you seem to have it otherwise you would not be able to select MySQL connection)
• Additional components (T4 templates/Workflow activities) required to support Model First flow - these are EF specific but typically installed as part of the DDEX provider - you seem to be missing this or you did not select the T4 template/workflow specific to your provider (open the edmx file and go to the properties window there are two options there that are interesting from DDL generation perspective - the "Database Generation Workflow" and the "DDL Generation Template". Click the dropdowns to see if you can select something that is MySQL specific)

Because you don't seem to have or use the workflow/T4 for MySQL (note I don't know if this is because you did not select the correct T4/workflow (see above) or MySQL DDEX does not support this or the installation is for whatever reason broken) EF Designer is using the default workflow which is for Sql Server. The default workflow relies on being able to find EF providers (for SqlServer and SqlServerCe) in the default path but the provider for MySQL is not there - hence the error. Note that the workflow and T4 templates for creating DDL are provider specific so even if you copied the MySQL provider to the default path it would not really work - you would get a peculiar result of SQL Server specific SQL Script using MySQL types.

We have a bug on improving this a little bit by providing a better guidance instead of just invoking the default DDL generation which we know will not work. You can also find some details in this bug.

I would suggest checking if MySQL components you are using do support Model First. An alternative would be just to move to Code First which only requires the EF runtime provider you already have to generate the DDL.

Answer 2

The other answer already kind of says this, but it's buried in his long post.

Notice that the error message refers to SSDLToSQL10.tt. You don't want that .tt file, you want the MySQL one. In the EF designer properties window, find the DDL Generation Template property and change it from SSDLToSQL10.tt to SSDLToMySQL.tt.