EF6 MySQL StrongTypingException When Column is not PK

c# entity-framework mysql

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

1
9
12/19/2016 7:42:48 PM

Popular Answer

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.

12
5/29/2016 6:22:25 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow