MySQL Bugs: #88507: Adding "ADO.NET Entity Data Model" as "EF Designer from database" does not work

Bug #88507	Adding "ADO.NET Entity Data Model" as "EF Designer from database" does not work
Submitted:	15 Nov 2017 22:16	Modified:	31 Mar 2018 16:32
Reporter:	Matt Bilek	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Visual Studio Integration	Severity:	S1 (Critical)
Version:	1.2.7, 1.2.9	OS:	Windows (Microsoft Windows 10)
Assigned to:		CPU Architecture:	Any
Tags:	ADO.NET Entity Data Model, EF Designer from database, Entity Framework 6.x, mysql for visual studio, MySQL Visual Studio Integration

Description:
When attempting to add an "ADO.NET Entity Data Model" as "EF Designer from database" using a successfully tested connection, once the "Finish" button is clicked the dialog box disappears and the Model was not created nor the Web.Config file was updated.  It does not matter if the radio button is set to "No" or "Yes" for sensitive data in the connection string.  Also, the Model is still not generated when the Web.Config checkbox is unchecked.  I have attempted countless permutations/combinations of the following versions with no avail.

Requirements:
MySQL Server 5.7 or greater
Microsoft Entity Framework 6.x
Microsoft Visual Studio 2017

NOTE: Please do not not give me other versions to try.  I have already attempted multiple combinations of versions.  Need an actual combination that has be tested and proven.  I have also tried just about every workaround that has been posted on the internet with luck.

Or is this a bug?

How to repeat:
Add an "ADO.NET Entity Data Model" as "EF Designer from database" using a successfully tested connection, once the "Finish" button is clicked the dialog box disappears and the Model was not created nor the Web.Config file was updated.

Versions used but not limited to:
MySQL Server - 5.7.20
MySQL Connector/Net - 6.9.9
MySQL for Visual Studio - 1.2.7
Visual Studio 2017 Enterprise - 15.4.4
.NET Framework - 4.6.1
Nuget:
Entity.Framework - 6.2.0
MySQL.Data - 6.9.10
MySQL.Data.Entity - 6.9.10
MySQL.Web - 6.9.10

My comment should be:
I have also tried just about every workaround that has been posted on the internet with NO luck.

Hello MB,

Thank you for the bug report.
Verified this behavior on Visual Studio 2017 and MySQL Visual Studio 1.2.7 version.

Thanks,
Chiranjeevi.

Chiranjeevi,

Thanks for the confirmation.  Please let me know of the timing and ETA of when there will be a fix.  Patiently waiting...

Thank you,
Matt

http://bugs.mysql.com/bug.php?id=88683 marked as duplicate of this one.

Hi there, I'm not sure if it's kind of late but I just already deal with this problem:
1. Actually ADO works with MySQL Data Entity 6.9.10 in Visual Studio 201x
2. You should download Connector/NET 6.9.10 form MySQL website
3. It is not necessary to modify your web.config

I have this:
1. MySQL Server - 5.7.21
2. MySQL for Visual Studio 1.2.7
3. Connector/NET 6.9.10

Let me know.

Unfortunately need to have this work with Entity.Framework - 6.2.0.  Please view above notes.

As general practice, we should be able to use the combination of all latest releases with no restrictions, failure of backwards compatability, lack of functionality or even worse bugs and errors.

Sincerely,
Matt Bilek

Any new news on this one? I can confirm that this bug still exists despite version updates as can be seen below. The dialog window in Visual Studio just seem to crash according to the above description and there is no message in the output window what so ever. Is there a way a debug output somewhere in a file that can be found to bring some clarity in what goes wrong?  

My configurations displaying the latest stable versions of:
MySQL Server - 8.0.11
MySQL Connector/Net - 8.0.11
MySQL for Visual Studio - 1.2.8
Visual Studio 2017 Enterprise - 15.7.2
.NET Framework - 4.7.1
Nuget:
Entity.Framework - 6.2.0
MySQL.Data - 8.0.11
MySQL.Data.Entity - 6.10.7

I can confirm that this problem still exists.  I have spent days attempting
to find a way to make Entity framework in VS work with MySQL, and have also
tried multiple combinations of editions and releases to try and get it to work.
I had tried to get Oracle to help with their bug, but they ended up trying to 
sell me a service contract....to fix their own bug!

My configuration
MySQL Installer - 1.4.27.0
MySQL Server - 8.0.12
MySQL Connector/Net - 8.0.12
MySQL for Visual Studio - 1.2.8
Visual Studio 2017 Community - 15.8.7
.NET Framework - 4.6.1
Nuget:
Entity.Framework - 6.2.0
MySQL.Data - 6.10.8
MySQL.Data.Entity - 6.10.8

Seems to work for me if I upgrade everything to 8.0.17

My configuration
MySQL Server - 5.7.27
MySQL Connector/Net - 8.0.17
MySQL for Visual Studio - 1.2.8
Visual Studio 2017 Community - 15.9.15
.NET Framework - 4.7.2
Nuget:
Entity.Framework - 6.2.0
MySQL.Data - 8.0.17
MySQL.Data.EntityFramework - 8.0.17

My previously known working configuration
MySQL Server - 5.7.27
MySQL Connector/Net - 6.9.12
MySQL for Visual Studio - 1.2.8
Visual Studio 2017 Community - 15.9.15
.NET Framework - 4.7.2
Nuget:
Entity.Framework - 6.2.0
MySQL.Data - 6.9.12
MySQL.Data.Entity - 6.9.12

Bug #98325 marked as duplicate of this one.

This bug seems to occur again on the latest version
MySQL.Data - 8.0.20
MySQL.Data.EntityFramework - 8.0.20

Downgrading to
MySQL.Data - 8.0.17
MySQL.Data.EntityFramework - 8.0.17
solved the issue for me

I have the same problem now after updating to MySQL Connector.NET 8.0.20 after clic on Next

Still exists on 8.0.23

The problem still exists at VS2019 ENTERPRISE with 8.0.23, MySQL 5.7.

What is the solution?

I found a combination of versions of MySql.Data, MySql.Data.Entrityframewor, MySql Connector NET and definition of the project's .config file, with which it is possible to create an ADO.NET Entity Framework model from a MySQL 8.0.11 database . However, I found another error running the code of a query with DbContext, and of which, I also found another solution.

To test these solutions, I suggest using a test computer in which you can completely remove all MySQL components, and even modify the Windows registry if necessary.

SOLUTION 1. CREATE ADO.NET ENTITY FRAMEWORK MODELS.
1) Completely uninstall all MySQL components as explained at https://answers.microsoft.com/en-us/windows/forum/all/how-to-completely-uninstall-mysql/e9... -77b271ae66ed #:% 7E: text = Go% 20to% 20Control% 20Panel% 20% 3EPrograms, sure% 20to% 20back% 20it% 20up.

2) Install MySQL 8.0.11. Do not include NET 8.0.11 connector. For non-MySQL expert users like my case, install using MySQL Installer, and also do the basic MySQL configuration with this tool. Restart the computer.

Download and install mysql-connector-net-8.0.23.msi: from
https://dev.mysql.com/downloads/connector/net/

Download and install mysql-for-visualstudio-1.2.9.msi (it is the latest version available): this is because MySQL Installer 8.0.11 does not show it in the components to install. Download it from https://dev.mysql.com/downloads/windows/visualstudio/

3) With MySQL Workbench or similar tool, create a database and test table in MySQL.

4) Using VS2019 Community using the Server Explorer create a connection to the test database. Check it out.

5) Using VS2019 Community create .NET Framework 4.5.2 console application as is, without installing any Nuget package. I suggest creating a new app while verifying that the solution works. I have not tested with .NET Core.

6) In Server Explorer select and open the connection to MySql. In the Solution Explorer select the console project created. Now in the upper right corner click on the icon of the MySQL Application Configuration tool. It is explained at https://dev.mysql.com/doc/visual-studio/en/visual-studio-app-config-ef.html.

This tool automatically installs the required Nuget packages based on the version of Entity Framework, MySQL Connector .NET. It also modifies the project's .config file.

Nuget Packages:

EntityFramework v6.1.3 by Microsoft
MySql.Data v8.0.23 by Oracle
MySql.Data.EntityFramework v8.0.23 by Oracle

App.config:

<? xml version = "1.0" encoding = "utf-8"?>
<configuration>
<configSections>
<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.2" />
</startup>
<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.20, Culture = neutral, PublicKeyToken = c5687fc88969c44d "/>
</DbProviderFactories>
</system.data>
<entityFramework>
<defaultConnectionFactory type = "MySql.Data.EntityFramework.MySqlConnectionFactory, MySql.Data.EntityFramework">
<parameters>
<parameter value = "v11.0" />
</parameters>
</defaultConnectionFactory>
<providers>
<provider invariantName = "MySql.Data.MySqlClient" type = "MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.EntityFramework, Version = 8.0.20, Culture = neutral, PublicKeyToken = c5687fc88969c44en = c5687fc88969c44en = c5687d" /88969c44
<provider invariantName = "System.Data.SqlClient" type = "System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>

</configuration>

7) Create an ADO.NET Entity data model from the MySQL database. In my case, the Visual Studio wizard did the whole process without any problem, and the model was created with the selected tables.

PROBLEM 2:

I was happy after several failed attempts and readings of posts on the matter. But it didn't last long for me, because when testing a simple query using the generated DbContext with the ADO.NET Model, an error was thrown.

CONSULTATION:
TelefonosEntities db = new TelefonosEntities ();
 var data = db.Nombres.ToList ();

ERROR:
System.Data.Entity.Core.EntityCommandExecutionException
  HResult = 0x8013193C
  Message = An error occurred while executing the command definition. See the inner exception for details.
  Source = EntityFramework
  StackTrace:
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands (EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute [TResultType] (ObjectContext context, ObjectParameterCollection parameterValues)
   at System.Data.Entity.Core.Objects.ObjectQuery`1. <> c__DisplayClass7. <GetResults> b__6 ()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction [T] (Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectQuery`1. <> c__DisplayClass7. <GetResults> b__5 ()
   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute [TResult] (Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults (Nullable`1 forMergeOption)
   at System.Data.Entity.Core.Objects.ObjectQuery`1. <System.Collections.Generic.IEnumerable <T> .GetEnumerator> b__0 ()
   at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext ()
   at System.Collections.Generic.List`1..ctor (IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList [TSource] (IEnumerable`1 source)
   at ConApp2.Program.Main (String [] args) in C: \ Users \ alicia \ source \ repos \ CoNApp1 \ ConApp2 \ Program.cs: line 46

  This exception was originally thrown at this call stack:
    [External Code]

Inner Exception 1:
MySqlException: Table 'telefonos.telefonos.nombres' doesn't exist

CAUSE: it is the MySQL Connector NET v8.0.23, and it also happens with the version v8.0.22, v8.0.21. See https://bugs.mysql.com/bug.php?id=101236

SOLUTION 2:
I did several failed tests of combinations of MySQL, MySQL Connector, but the one that finally solved the problem for .NET Framework projects was the following:

MySQL:
- MySQL v8.0.20 or MySQL v8.0.23
- MySql Connector v8.0.20
- MySql for Visual Studio 1.2.9

Nuget Packages:
- EntityFramework v6.1.3
- MySQL CONNECTOR NET v8.0.20.
- MySql.Data v8.0.20
- MySql.Data.EntityFramework v8.0.20

PROBLEM 3:
In .Net Framework 4.5.2 web projects, the Ado.Net Entity Framework data model creation wizard closes automatically after selecting the MySql connection to use.

TEMPORARY SOLUTION 3:
Use a .NET Class Library project to use the Ado.Net Entity Framework data model, and reference it in the web project.

Bug #102236 marked as duplicate of this one.