Entity Framework with MySQL - Booleans, Bits and "String was not recognized as a valid boolean" errors.

In my current role, I'm using MySQL v5.6 as the database engine for an ASP.NET MVC web application which also uses Entity Framework v6.1.3 as the ORM to connect to the backend database, doing so via the use of the MySQL .NET Connector ADO.NET driver.

It appears that there are some "interesting" issues regarding how MySQL (and the .NET Connector) handle and model boolean entity properties, modelling them as a TINYINT(1) column by default, and causing odd and spurious errors, such as the "String was not recognized as a valid Boolean" error when performing entirely innocuous Entity Framework functions within your code.

The MySQL .NET Connector contains a bug whereby database columns that are generated for any boolean property of an entity, by default, get modelled with a TINYINT(1) data type rather than the more appropriate BIT datatype.  This, in itself, isn't the bug, however the strange usage of a TINYINT(1) data type for a boolean column rather than the seemingly more appropriate BIT(1) datatype does enable the bug to subsequently raise it's head.

In using a TINYINT(1) data type by default, this causes the MySQL .NET Connector to occasionally throw an exception which upon examination contains the error string, "String was not recognized as a valid Boolean".  This occurs when performing such innocuous functionality as using a number of Entity Framework [Include] (https://msdn.microsoft.com/en-us/library/bb738708(v=vs.110).aspx) methods when attempting to retrieve a large enough graph of objects from the database.  For example, code such as this:

var results = dbContext.SocietyMemberships
                .Include(m => m.TenantPersona),
                .Include(m => m.Society),
                .Include(m => m.IncomeTypeGroups),
                .Include(m => m.IncomeTypeGroups.Select(itg => itg.IncomeTypeGroup)),
                .Include(m => m.IncludeTerritories),
                .Include(m => m.ExcludeTerritories);

return results.ToList();

Would result in the aforementioned exception being throw.  However, take out a few of those "includes" and the query works perfectly with no exception thrown.

It appears that the spurious exception is caused by the MySQL .NET Connector's attempt to generate the necessary SQL statement required for the query and subsequent parsing of that SQL statement would fail to parse any included TINYINT(1) columns as boolean values. In my (albeit limited) testing, it appeared that include'ing 5 or less related tables did not display the error, but Including more than 5 tables would cause the error consistently.

In order to fix the issue, we simply have to ensure that the MySQL connector, via Entity Framework, will model all Boolean entity properties as a BIT(1) data type column.  This must be done explicitly in code using code such as the following:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    
    // other initialization code here...
    
    modelBuilder.Properties<bool>().Configure(c => c.HasColumnType("bit"));
}

Performing this configuration in the OnModelCreating method of the DbContext object ensures that this configuration applies to all entities, both those already existing and any new ones created in the future,  ensuring they are modelled with a BIT(1) column data type rather than a TINYINT(1) data type.

It should be noted that the same "String was not recognized as a valid Boolean" exception can be thrown when using other Entity Framework functionality, however, I've yet to pin down exactly what code can cause the error.  I suspect that, due to the nature of the error as detailed above, there's an incredible amount of different C# code that, when translated to the MySQL SQL dialect, generates SQL of sufficient amount and complexity that the exception is thrown.

Finally, it would appear that the choice of the MySQL .NET Connector to use TINYINT(1) as the default column data type instead of BIT(1) stems from the history of the BIT data type in MySQL.  An old blog post from Baron Schwartz that documents this history and behaviour states,

MySQL has supported the BIT data type for a long time, but only as a synonym for TINYINT(1) until version 5.0.3. Once the column was created, MySQL no longer knew it had been created with BIT columns. In version 5.0.3 a native BIT data type was introduced for MyISAM, and shortly thereafter for other storage engines as well. This type behaves very differently from TINYINT.

He goes on to indicate how this behaviour has changed over time:

The old data type behaved just like the small integer value it really was, with a range from -128 to 127. The new data type is a totally different animal. It’s not a single bit. It’s a fixed-width “bit-field value,” which can be from 1 to 64 bits wide. This means it doesn’t store a single BIT value; it’s something akin to the ENUM and SET types. The data seems to be stored as a BINARY value, even though the documentation lists it as a “numeric type,” in the same category as the other numeric types. The data isn’t treated the same as a numeric value in queries, however. Comparisons to numeric values don’t always work as expected.

This change in behavior means it’s not safe to use the BIT type in earlier versions and assume upgrades will go smoothly.

It's from this chequered history that it appears that such issues converting from "BIT" columns (that can potentially have a large range of actual values) to a binary boolean have arisen.