MySQL 5.7: System.Data.StrongTypingException when adding or updating an EDMX model

entity-framework entity-framework-6 mysql mysql-5.7

Question

I try to get the Entity Framework (5 or 6) working with MySQL 5.7 (database first), but adding an Entity Data Model for a (test) database results in the error "Unexpected exception occured when generating the model. StrongTypingException: The value for column 'IsPrimaryKey' in table TableDetails is DBNull.". The table has a primary key by the way:

CREATE TABLE `test` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Value` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

In my attempts to get to the source of this issue I tried some combinations:

  • MySQL 5.6 with the EF 5 works. I can add, update and use an EDMX model.
  • MySQL 5.6 with the EF 6 works partly. When adding an EDMX model the wizard crashes. I can add a model for the EF 5, update EF to 6, install the Nuget package 'MySql.Data.Entity' and update the EDMX model to the EF 6. The model works when running the application. But trying to update the model from the database results in an exception.
  • After creating an EF 6 model like above and using it on a machine with MySQL 5.7 the model works, but updating (again) results in the StrongTypingException.

I hope that this issue can somehow be solved via some MySQL configuration. I already tried show_compatibility_56=ON on the machine with MySQL 5.7. Did not help.

1
2
11/7/2015 1:14:09 PM

Popular Answer

Update a 3rd time: Execute the queries below: you need BOTH global and non-global switches to be OFF. I have proved these to work on VS2015 + EF6.1.3 + MySql 5.7.12

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

After settings these values you do NEED to restart Visual Studio before attempting to create EDMX.

If you don't see results go check if both variables are correctly set by:

select @@optimizer_switch;
select @@GLOBAL.optimizer_switch;

Both queries should give you a result with "derived_merge=off" found at the end of the variable value string.

NOTE: If you restart the server these values needs to be set again because the derived_merge optimizer_switch is default to be ON.

Reference: There has been active discussion on this BUG for EF6.1.3 and MySql 5.7 https://bugs.mysql.com/bug.php?id=79163

4
6/23/2017 12:12:11 AM


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