ADO.NET Entity Data Model fails to be created from MySql Database + using Visual Studio

Question

I am trying to add ADO.NET Entity Data Model generated from MySql Database(8.0.21.0), I have installed everything as expected (MySql Connector, MySql for Visual Studio) and after selecting the tables I want to include I receive the below exception.

MySql 8.0.21.0, Visual Studio 2019, Entity framework 6.4.4, Console Application

Unable to generate the model because of the following exception: 'System.Data.Entity.Core.EntityCommandExecutionException: 
  An error occurred while executing the command definition. 
  See the inner exception for details. 
  ---> MySql.Data.MySqlClient.MySqlException: Fatal error encountered during command execution. 
  ---> MySql.Data.MySqlClient.MySqlException: Fatal error encountered attempting to read the resultset. 
  ---> MySql.Data.MySqlClient.MySqlException: Reading from the stream has failed. 
  ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. 
  ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
   at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   --- End of inner exception stack trace ---
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   at System.Net.FixedSizeReader.ReadPacket(Byte[] buffer, Int32 offset, Int32 count)
   at System.Net.Security._SslStream.StartFrameHeader(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security._SslStream.StartReading(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security._SslStream.ProcessRead(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest)
   at System.Net.Security.SslStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.TimedStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.ReadFully(Stream stream, Byte[] buffer, Int32 offset, Int32 count)
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.MySqlStream.LoadPacket()
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   --- End of inner exception stack trace ---
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.EntityFramework.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.Execute(EntityCommand entityCommand, CommandBehavior behavior)
   at System.Data.Entity.Core.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.FunctionDetailsReader..ctor(EntityCommand command, Version storeSchemaModelVersion)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadFunctionDetails(IEnumerable`1 filters)
   at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.EntityStoreSchemaGeneratorDatabaseSchemaLoader.LoadStoreSchemaDetails(IList`1 filters)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GetStoreSchemaDetails(StoreSchemaConnectionFactory connectionFactory)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.CreateStoreModel()
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GenerateModel(List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModels(String storeModelNamespace, ModelBuilderSettings settings, List`1 errors)
   at Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModel(ModelBuilderSettings settings, IVsUtils vsUtils, ModelBuilderEngineHostContext hostContext)'.
Loading metadata from the database took 00:00:00.4895530.
Generating the model took 00:00:09.6119343.

Answer 1

I've been struggling with the same problem for 2 days. I was using MySQL 8.0.18 (Server, NET Connector etc.), MySql for Visual Studio v1.2.9, nuget packages: MySql.Data + MySql.Data.EntityFramework and everything was working just fine a month a ago. Last week i updated the nuget packages to v8.0.21 and the db connection got disturbed so i updated my MySql to version v8.0.21. I made changes to the database and when i used the ADO.NET Entity Data Model wizard, i got the same exception. Tried everything but it did not work so i did the following to go back to the past working state:

Note: If you plan to follow, I advise you to follow in this order:

1. First install MySql for Visual Studio v1.2.9
2. Create all your current MySql database backups. Uninstall MySql completely from your system with the help of this guide Completely Uninstall MySql. Then restart your system.
3. Download and Install MySql v8.0.18 (all components including NET Connector)
4. Restore all your databases back in this installed version.
5. In your project, you may also have to downgrade the packages. I downgraded EntityFramework to v6.2.0, MySql.Data and MySql.Data.EntityFramework to v8.0.18
6. Now Create a new ADO.NET Entity Data Model and things should work this time.

I tried everything using the latest versions of MySql v8.0.21 and packages but couldn't get things to work. One possible reason could be that the MySql NET Connector and the target .NET framework version of your application may be incompatible with each other. It may also be the same case between MySql NET Connector version and Visual Studio version. This may not be the actual solution to your problem but it's one way of getting it to work.

Answer 2

I've checked this for MySQL 8.0.22 it works fine.

I had these errors in 8.0.21 too. So i decided to check upgrade instead of downgrade. It's really ubset that we are getting this problems with 'new' version.

Also, if you had project configured with MySQL Configuration Tool for previous version run it, uncheck 'Use MySQL with Entity Framework', click Finish. Reopen and check it.

I didn't just upgrade it, firstly i've deleted 8.0.21 through MySQL Installer (didn't use complete uninstall at all). And after it i have installed newest version. MySQL Visual Studio Tool i install separately by other .msi from MySQL downloads page

Answer 3

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

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=c5687fc88969c44d" />
            <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.