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

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

Question

Adding an Entity Data Model for a (test) database causes the issue when I try to get the Entity Framework (5 or 6) running with MySQL 5.7 (database first) "The model generation encountered an unexpected exception. StrongTypingException: The value for column 'IsPrimaryKey' in table TableDetails is DBNull.". By the way, the table has a primary key:

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;

I tried a several combinations to try and identify the root of this problem, including:

  • MySQL 5.6 and EF 5 are functional. An EDMX model can be used, updated, and added to.
  • Partially functional MySQL 5.6 with EF 6. The wizard crashes when an EDMX model is added. I may install the Nuget package "MySql.Data.Entity" and update the EDMX model to the EF 6 while adding a model for the EF 5. When the program is running, the model functions. An exception is raised when the model is attempted to be updated from the database.
  • The above-mentioned EF 6 model works when created and used on a machine with MySQL 5.7, but upgrading (again) causes the StrongTypingException.

I'm hoping that some MySQL setup may help to resolve this problem. Already, I triedshow_compatibility_56=ON on the computer running MySQL 5.7. did not assist.

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

Popular Answer

A third update: Execute the following queries: Both the global and non-global switches must be turned off. On VS2015 + EF6.1.3 + MySql 5.7.12, I have demonstrated that they work.

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

You DO NEED to restart Visual Studio after changing these variables before attempting to generate EDMX.

Check to see if both variables are properly set if you don't see results by:

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

You should receive a response from both queries that has "derived merge=off" at the end of the variable value string.

NOTE: Because the derived merge optimizer switch is set by default to ON, these settings must be adjusted again whenever you restart the server.

Referral: This BUG for EF6.1.3 and MySql 5.7, https://bugs.mysql.com/bug.php?id=79163, has a lively debate.

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