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:
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.
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