Workaround for MySql 5.7 EF DbFirst Issue

Anyone working on .Net application with MySql 5.7 and EF in Db First Approach would have come across what is an officially confirmed bug.  This bug rises when you attempt to generate/update your Entity Model after changes to the Database.

“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:”

The workaround you need to do to fix this one

  1. Open your Workbench and execute following commands on your Db.
set global optimizer_switch='derived_merge=off';
set optimizer_switch='derived_merge=off';
  1. Close your workbench ( you might have to restart your visual studio as well ).
  2. Generate/Update your model.
That should work.
Advertisements

Specifying singular table names

Entity Framework usually generates Tables with plural names. For example, a Student class would generate a table Students. This could be a little inconvenience for teams who insists on plural names. The solution is to override the ‘OnModelCreating’ Method to remove the plural features.

image

This would ensure plural names are used in every concerned Context. Now the question is, what if we need to give plural name only for selected table names.

image

This would ensure that the Standard Table is created with a plural name. Overriding OnModelCreating is very useful method to have when you need to provide customization of tables that is being generated by Entity Framework.

Composite Primary Key using Data Annotations

When developing domain classes in Entity Framework using Code First Approach, the default code conventions creates a primary key of a property named “ID” or <ClassName>ID. But if you wanted to use another property as your primary key , you can use DataAnnotations and decorate the property using “Key” attribute.

What is more interesting is that you could actually create a composite primary key. All you need to do is use another attribute along with “Key”, namely, “Column”.

image

The above example creates a Table with a Composite Primary Key made up of Dept1 and Dept2. Entity Framework is surely letting the developers live in the comfort zone of Visual Studio rather than dig up SQL Server.