EF6 MySQL StrongTypingException When Column is not PK

Question

We are using MySql and Entity FrameWork with VS 2013 those are the tools installed:

• MySql Server 5.7.8
• MySql WorkBench 6.3.
• MySql for Visual Studio 1.2.4
• Connector/NET 6.9.
• VS 2013 Ultimate
• Entity FrameWork 6.1.3 installed via NuGet

We have imported the necesary libraries into the project MySQLWeb, MySQL.Data, Mysql.Data.Emtity.EF6

We created a schema with MySQLWorkBench with a simple table like this:

CREATE TABLE `persona` (
  `idpersona` int(11) NOT NULL,
  `nombre` int(11) DEFAULT NULL,
  PRIMARY KEY (`idpersona`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The problem we have is that wuen we create the ADO.net Entity Data Model we connect right to the database but then it not creates the model and show the following error:

'System.Data.StrongTypingException: El valor de la columna 'IsPrimaryKey' de la tabla 'TableDetails' es DBNull. ---> System.InvalidCastException: La conversión especificada no es válida. en Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.TableDetailsRow.get_IsPrimaryKey() --- Fin del seguimiento de la pila de la excepción interna --- en Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.TableDetailsRow.get_IsPrimaryKey() en Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateProperties(IList1 columns, IList1 errors, List1& keyColumns, List1& excludedColumns, List1& invalidKeyTypeColumns) en Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateEntityType(IList1 columns, Boolean& needsDefiningQuery) en Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateEntitySets(IEnumerable1 tableDetailsRows, EntityRegister entityRegister, IList1 entitySetsForReadOnlyEntityTypes, DbObjectType objectType) en Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateEntitySets(IEnumerable1 tableDetailsRowsForTables, IEnumerable1 tableDetailsRowsForViews, EntityRegister entityRegister) en Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.Build(StoreSchemaDetails storeSchemaDetails) en Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.CreateStoreModel() en Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelGenerator.GenerateModel(List1 errors) en Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModels(String storeModelNamespace, ModelBuilderSettings settings, List1 errors)
en Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModel(ModelBuilderSettings settings, IVsUtils vsUtils, ModelBuilderEngineHostContext hostContext)'. La carga de los metadatos desde la base de datos tardó 00:00:00.4029113. La generación del modelo tardó 00:03:36.0692240.

We have proved that if we make all single columns as Primary Key everything works fine, the problem happens when one column is not Primary Key it does not matter the type of the column.

I didn´t find anyone with same problem on internet.

Thank you very much for reading my question

regards

Answer 1

Entity Framework (version 6.1.3) and MySQL Server (5.7)

One way to resolve the issue is,

1. Open Services (services.msc) and restart MySQL57 service.

2. Execute the following commands in MySQL.

   use <<database name>> set global optimizer_switch='derived_merge=OFF';

3. Update the .edmx.

Answer 2

I have pinpointed that this error appears only to connections with 5.7.8 mysql server which is in release candidate state. With the latest stable version 5.6.25 the entity framework works correctly.

Answer 3

Here is the english version of the error. It also applies for VS2015 and MySQL.Data 6.9.7

    Unable to generate the model because of the following exception: 'System.Data.StrongTypingException: The value for column 'IsPrimaryKey' in table 'TableDetails' is DBNull. ---> System.InvalidCastException: Specified cast is not valid.
    at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.TableDetailsRow.get_IsPrimaryKey()
    --- End of inner exception stack trace ---
    at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.SchemaDiscovery.TableDetailsRow.get_IsPrimaryKey()
    at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateProperties(IList`1 columns, IList`1 errors, List`1& keyColumns, List`1& excludedColumns, List`1& invalidKeyTypeColumns)
    at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateEntityType(IList`1 columns, Boolean& needsDefiningQuery)
    at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateEntitySets(IEnumerable`1 tableDetailsRows, EntityRegister entityRegister, IList`1 entitySetsForReadOnlyEntityTypes, DbObjectType objectType)
    at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateEntitySets(IEnumerable`1 tableDetailsRowsForTables, IEnumerable`1 tableDetailsRowsForViews, EntityRegister entityRegister)
    at Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.Build(StoreSchemaDetails storeSchemaDetails)
    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)'.

Answer 4

I had the same problem, I solved it uninstalling mysql server 5.7 x64 and intallig mysql server 5.5 x86.

I hope it helps