My first forays into running Entity Framework with MySQL have been entertaining to say the least. Each challenge seems to have followed the next and required a fair bit of research to solve so I thought it was worth compiling them all into one blog post. It’s an aide memoir for me and if it helps you readers out too, all the better!
bit vs tinyint for booleans
By default, EF will handle booleans as tinyint under the hood as MySQL in general has no built in concept of true | false other than 1 | 0 and uses tinyint(1) as a substitute. However, tinyint gets treated as byte rather than bit by EF so it’s all a ‘bit’ confusing (ahem, sorry)… Luckily, there is an easy code fix.
symptoms
Error messages usually seen in this case:
System.FormatException: String was not recognized as a valid Boolean..fix
Add this to your database Context code:
modelBuilder.Properties()
.Where(x => x.PropertyType == typeof(bool))
.Configure(x => x.HasColumnType("bit"));inheritance
Unlike with MSSQL, when using EF6 with MySQL, only one entity in the hierarchy can be concrete – the leaf. This can have implications for your design if you have complex similar entities and want to abstract out those things they share in common.
symptoms
Error messages usually seen in this case:
The EntitySet 'CClass' is not defined in the EntityContainer 'TestContext'fix
- Redesign
In a hierarchy such as
MailBox ← ExchangeMailBox ← Exchange2016MailBox
only Exchange2016MailBox can be concrete
- Manually map inherited fields to the table in your Context via the OnModelCreating method using
modelBuilder.Entity().Map(m => m.MapInheritedProperties()).ToTable("Employee");unique indexes
Where the design calls for a unique index on a varchar field (e.g. email address) this can be inhibited by strong internal database limits. For the unique index to work on MySQL, there is a max length that must be applied to the field. The exact value is dependent on the collation and database engine used and relates to an underlying byte value.
- MyISAM allows keys of length 1000 bytes.
- InnoDB allows keys of length 767 bytes.
On ascii (latin-1), characters are assumed to be stored as 1 byte, giving varchar(767) or above. However, UTF8 collations will store a char in between 1 and 3 bytes. Expect to be able to use max varchar(254).
For keys with collation utf8mb4 or UTF-16, the byte length per character is assumed as 4 bytes allowing a maximum of varchar(250) on MyISAM or varchar(190) on InnoDB.
symptoms
Error messages usually seen in this case:
Specified key was too long; max key length is 767 bytesfix
- consider changing your collation; does your internalisation policy require UTF-16 or only UTF-8?
- consider changing your database engine – loads of StackOverflow articles address this InnoDB vs MyISAM debate, there’s even an article on wikipedia (to be read with the appropriate level of salt)
- enforce the appropriate maximum length
- enforce uniqueness programatically (and trust you don’t miss a use case in the code base)
performance issues
Although Entity Framework 6 supports the Find linq method it is not performant and leads to some very inefficient sql query creation under the hood. Big performance improvements can be made by using Single or SingleOrDefault instead.
If you have a complex hierarchy of entities it’s worth considering the question of database policy Table-Per-Heirarchy vs Table-Per-Type. There are good articles out there explaining the difference and suggesting that TPH provides better performance, particularly on Entity Framework. It makes use of a discriminator column which brings its own design decisions. Historically there have been reports of issues with discriminator columns in earlier releases of EF6 but these appear to have been fixed.
Beyond that changing the underlying database to MSSQL or adopting a different ORM may be your only options. Looking ahead, EF7 does not yet support MySQL and no one knows when Oracle will change this so it’s worth investigating other solutions.