dbnull mysql,MySQL - 實體：表'TableDetails'中的列'IsPrimaryKey'的值為DBNull

I am using Visual Studio 2013 with Entity Framework 5 and MySQL Server 5.7.9.

When trying to create a Model from the database (or 'Update Model From Database') the following message appears:

'System.Data.StrongTypingException: The value for column

'IsPrimaryKey' in table 'TableDetails' is DBNull . ---> System.InvalidCastException: Specified cast is not valid.

I know that this question has been asked before, but i haven't find any solution.

Also i don't have the option of downgrading to MySQL 5.6.

The problem occurs even for a simple table.

The sample table

CREATE TABLE new_table

(

id int(11) NOT NULL AUTO_INCREMENT,

name varchar(45) NOT NULL,

PRIMARY KEY (id)

)

ENGINE = InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

If the table consists only from the Primary Key then the model is being created as it should.

EDIT:

If i make both the fields PRIMARY Keys the model is being created without any errors.

Does anyone have any idea about this?

Kind Regards.

The full error stack:

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(IList1

columns, IList1 errors, List1& keyColumns, List1& excludedColumns,

List1& invalidKeyTypeColumns) at

Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateEntityType(IList1

columns, Boolean& needsDefiningQuery) at

Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateEntitySets(IEnumerable1

tableDetailsRows, EntityRegister entityRegister, IList1

entitySetsForReadOnlyEntityTypes, DbObjectType objectType) at

Microsoft.Data.Entity.Design.VersioningFacade.ReverseEngineerDb.StoreModelBuilder.CreateEntitySets(IEnumerable1

tableDetailsRowsForTables, IEnumerable1 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(List1

errors) at

Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModels(String

storeModelNamespace, List1 errors) at

Microsoft.Data.Entity.Design.VisualStudio.ModelWizard.Engine.ModelBuilderEngine.GenerateModel(EdmxHelper

edmxHelper)'. Loading metadata from the database took

00:00:00.5856317.

解決方案

Entity Framework (version 6.1.3) and MySQL Server (>= 5.7.6)

One way to resolve the issue is,

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

2. Execute the following commands in MySQL.

use <>;

set global optimizer_switch='derived_merge=OFF';

3. Update the .edmx.

It's a late reply. But hope it will help somebody.

Thanks.