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

Upgrading AutoMapper

On more than one or two occasions now, I've found myself working with a codebase that uses AutoMapper to perform mapping between objects, most commonly to map data entity objects to their equivalent domain objects.

These codebases often are using a version of AutoMapper prior to version 4.2 which is the version that AutoMapper decided to remove it's static API. I often find the need to upgrade the version of AutoMapper that's in use for any one of a number of different reasons and so I find myself having to re-write all of the AutoMapper initialisation code as the old way of creating mappings is no longer supported.

The problem here is that, in a large enough codebase, moving from purely static mappings as was done with AutoMapper prior to v4.2 to non-static mappings that would need to be either manually constructed or injected into all of the places in the codebase that would require them.  This is often a non-trivial feat and makes moving from static mappings to non-static mappings a much larger job that you might ideally like it to be.

The "proper" way to configure your AutoMaper mappings these days is write code similar to this:

var config = new MapperConfiguration(cfg => {
  cfg.CreateMap<SourceObject, DestinationObject>();
});

and then to pass that config object around, or inject it into the class that might need your mapping config.  Of course, if that class is short-lived, you'll need to re-create these mapping configs again and again.  Whilst this is undoubtedly the "best" approach and does not require any static objects, it's sometimes not immediately feasible in an a large, legacy codebase.

There is, however, a way of changing your old, static API-based, mapping configurations in such a way that they continue to use a static object - albeit not the same one as was in AutoMapper < v4.2 - such that the change can be a "drop-in" replacement without having a knock-on ripple effect through out the rest of the codebase.

The Old Way

So the old way of configuring AutoMapper mappings with the static API was something very similar to this:

public class AutomapperConfig
{
	public static void Configure()
	{
		Mapper.CreateMap<SourceObjectA, DestinationObjectA>();
		Mapper.CreateMap<SourceObjectB, DestinationObjectB>();
		Mapper.CreateMap<SourceObjectC, DestinationObjectC>();
	}
}

and the static Configure method would be called within some entry point of your program (i.e. inside the Application_Start method of the Global.asax.cs class)

The New Way

And here's the new way of performing the same thing.  A slight syntactical change to the code and it's a "drop-in" replacement.

public class AutomapperConfig
{
	public static void Configure()
	{
		Mapper.Initialize(cfg =>
		{
			cfg.CreateMap<SourceObjectA, DestinationObjectA>();
			cfg.CreateMap<SourceObjectB, DestinationObjectB>();
			cfg.CreateMap<SourceObjectC, DestinationObjectC>();
		}
	}
}

Note how we use the new Initialize method of the static Mapper object passing in a lambda that performs our mapping configurations rather than constructing a new instance of the MapperConfiguration object to pass to the relevant part of our code that needs to perform mappings.

And with that, we can easily upgrade from AutoMapper pre-v4.2 to a version post-v4.2 without the code changes being too onerous.

KnockoutJS binding on Select elements in a ForEach loop

In an application that I was working on recently, I had the need to bind a select element (i.e. a drop-down list) with KnockoutJS.  This is not an unusual thing to need to do with Knockout, however, this particular select element was ultimately rendered by Knockout itself as it was part of a collection of data objects, and so was within a Knockout foreach binding.

I had a collection of "rates" to choose from in one part of my Knockout View Model and needed to populate another part of the same Knockout View Model with the user's selected rate.  In this case, I wasn't simply selecting the numerical index value or some singular intrinsic data type as the value of the select element, but rather, I needed to whole underlying object.

So, our view model looks something like this:

{
	"MyViewModel" : {
		"Rates" : [{
				"Name" : "Initial Rate",
				"Percentage" : "80",
				"Categories" : [{
						"Id" : "b1fae11b-ce8d-4c72-ad4f-71dc515d0f42",
						"Name" : "Mechanical"
					}, {
						"Id" : "10e01f58-e7f5-4f2c-8d35-e2b87fc43a77",
						"Name" : "Performance"
					}
				]
			}, {
				"Name" : "Extended Rate",
				"Percentage" : "60",
				"Categories" : [{
						"Id" : "10e01f58-e7f5-4f2c-8d35-e2b87fc43a77",
						"Name" : "Performance"
					}, {
						"Id" : "d79a225b-4b76-4371-910d-47a9f3f58665",
						"Name" : "Sync"
					}
				]
			}
		],
		"OverrideRates" : []
	}
}

You can see that we're going to select one of the "Rates" from the array of Rate objects, and that each Rate object doesn't have it's own obvious unique identifier.  Once the user has selected a rate from the rates array, we want to copy the entire object into an OverrideRate object and store that in the OverrideRates array.  Our OverrideRate object will eventually look like this:

{
	"SelectedRate" : {
		"Name" : "Initial Rate",
		"Percentage" : "80",
		"Categories" : [{
				"Id" : "b1fae11b-ce8d-4c72-ad4f-71dc515d0f42",
				"Name" : "Mechanical"
			}, {
				"Id" : "10e01f58-e7f5-4f2c-8d35-e2b87fc43a77",
				"Name" : "Performance"
			}
		]
	},
	"OverridePercentage" : "90",
	"DateFrom" : "2017-01-01T00:00:00.000Z",
	"DateTo" : "2017-12-31T00:00:00.000Z"
}

So you can see here that the entire selected Rate object has been copied from the Rates array into the SelectedRate property.

In order to achieve this, I had markup similar to the following:

<table>
	<tbody data-bind="foreach: MyViewModel.OverrideRates">
		<tr>
			<td>
				<div>
					<select data-bind="options: $root.Rates,
							optionsCaption: 'Please select a rate...',
							optionsText: function(item) {
								return item.Name() + ' (' +
								item.Categories().map(function(elem){ return elem.Name() }).join(', ') + ')'},
							value: $data.SelectedRate">
					</select>
				</div>
			</td>
		</tr>
	</tbody>
</table>

The user was presented with a select element like this:

The Knockout binding for the select element shows that we're telling Knockout to get the options available for selection from the $root.Rates property, which is the collection of available rates (the $root prefix is a special binding context telling Knockout to access the rates object from the root level of the View Model), and further we're telling Knockout to use a special user-defined inline function to take certain string properties of our Rate object and use them to build up the actual text that the user will see inside the select element (i.e. "Initial Rate (Mechanical, Performance)").  Finally, we tell knockout that the "value" of the selected option from the select element needs to be bound to the $data.SelectedRate property.  However, there was a problem.

Two way data binding and data contexts

Knockout's data binding works by having your view model's properties actually be observable functions rather than the data that you actually want to bind.  This means that your MyViewModel.Name property, which can be two-way data-bound to a input box for editing the name string, is actually a function.  The function, when invoked with no parameters (i.e. MyViewModel.Name() ) will return the underlying data - in this case the name string, whilst invoking the function with a parameter (i.e. MyViewModel.Name("Jimmy") ) will set the underlying data to the parameter value passed in.

Whenever Knockout is binding page elements to underlying view model properties inside a loop, you use the special $data binding context prefix in order to access the current object that is currently being processed as part of the the loop.  On all other elements (for example, an input element) this works just fine, since the data that Knockout has to bind is a single value - in the case of a textbox, it's a simple string:

<tbody data-bind="foreach: MyViewModel.Rates">
	<tr>
		<td data-bind="text: $data.Name"></td>
		
	</tr>    
</tbody>

$data gives us a reference to the current rate object within the loop, allowing us to access the Name property of the correct object.  Note that although the Name property of the object is actually a Knockout observable function, we don't need to add the parentheses at the end (i.e. $data.Name() isn't required) since Knockout's binding is clever enough to deal with that for us.

This is great for single, granular pieces of data, however, when attempting to use this binding context as part of my select element that needed not just a simple, singular value, but a whole object to be bound to it, things did work so well.

As the user was selecting a "rate" from the select element drop-down list, I wanted knockout to binding the entire Rate object to the SelectedRate property of the View Model.  This part of the data-bind attribute was intended to achieve that:

value: $data.SelectedRate

However, when examining the View Model with Google Chrome's debugging tools, I noticed that the view model's SelectedRate property was not being updated when the user changed the selection.  Bizarrely, I did notice that when Knockout performed other data binding, for example, when the user changed the value in a textbox that was related to the selected rate, the SelectedRate property was suddenly bound with the correct object!

This was quite strange behaviour, and actually turned out to be a bit of a red herring which was leading my debugging efforts awry.  After much Googling and trial and error, it turned out the issue was all down to observable functions and how they work with the data binding context.

When your $data context isn't

Turns out that when you use the $data binding context in a binding expression, $data is bound not to the Knockout observable function but to the returned value from invoking the function.  This means that $data.SelectedRate was simply returning the underlying values of the observables rather than the observable function's themselves (SelectedRate is also the underlying value since the call to $data is only returning a value, meaning that properties "hanging off" that are also not observables).  This fix is to use a different Knockout binding altogether, which is the $rawData binding context.

Knockout's own documentation describes this:

$rawData

This is the raw view model value in the current context. Usually this will be the same as $data, but if the view model provided to Knockout is wrapped in an observable, $data will be the unwrapped view model, and $rawData will be the observable itself.

 

(Emphasis above is mine).

So, we needed to use the $rawData binding context to ensure that data bound page elements, that are expected to be bound to a complex object, are correctly bound.  And that this is not required when data binding to a simple, intrinsic type (i.e. a string, number etc.)

Interestingly, a Stack Overflow answer to a similar question talks about the $data binding context only referring to the underlying value rather than the observable function, but offers a different solution, one which didn't seem to work correctly for me.

You might think that, since usage of the $rawData binding context is fully explained in the Knockout documentation, why did I not discover and resolve this issue earlier?  Well, it turns out that Knockout has a somewhat chequered history regarding its $data binding context.

Knockout broke my data binding!

Another Stack Overflow question gives us a clue to the history of Knockout's $data binding context.

It would appear that in versions of Knockout prior to Version 3.0, the $data binding context used to function the same as the current version's $rawData binding context works - that is to say that $data used to be bound to the observable function rather than the underlying value. This was entirely due to the fact that the $rawData binding context did not exist in Knockout prior to Version 3.0 and so was considered missing functionality.

With Knockout Version 3.0, the developers introduced the $rawData binding context, however, it was somewhat buggy.  In Knockout Version 3.1, they finally fixed it so that $rawData now correctly refers to the observable function (if one exists) rather than the underlying value, whilst $data continues to "unwrap" the observable and refer to the underlying data value.