MySQL Bugs: #93028: MySQL .net connector for EF Core treats incorrectly nullable boolean properties

Bug #93028	MySQL .net connector for EF Core treats incorrectly nullable boolean properties
Submitted:	31 Oct 2018 14:26	Modified:	3 Mar 2020 17:48
Reporter:	Cosmin Petrenciuc	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	8.0, 8.0.16	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	bool? System.Int16 coercion type

Description:
Create an .NET Core 2.1 project. This project will be using Entity Framework Core 2.1.

Define in this project a model class corresponding to a MySql table with a property having type bool? (nullable boolean). It will correspond to a field with type BIT(1) NULLABLE. So far so good.

Add a record to this table.

Then try to read this record using EF Core database context. An error will be generated saying there is no coercion available from type System.Int16 to System.Nullable[System.Boolean].

I do not understand why System.Int16 type is being used.

The same model can be successfuly used with "Pomelo.EntityFrameworkCore.MySql".

How to repeat:
Create an .NET Core 2.1 project. This project will be using Entity Framework Core 2.1.

Define in this project a model class corresponding to a MySql table with a property having type bool? (nullable boolean). It will correspond to a field with type BIT(1) NULLABLE. So far so good.

Add a record to this table.

Then try to read this record using EF Core database context. An error will be generated saying there is no coercion available from type System.Int16 to System.Nullable[System.Boolean].

The Connector/NET convention for ‘bool?’ fields is “BOOL NULLABLE” aka ”TINYINT(1) NULLABLE”. Can you change your DB column type?

The schema is created through Code First migrations.
The C# source code for creating the table is the following:

[Table("orgunit")]
public class OrgUnit : AuditableEntity, IIdentifiable<OrgUnit>
{
	#region Fields

	private DateTime? lastPasswordChange;

	#endregion

	#region Properties

	[NotMapped]
	private string name;

	[Key]
	[Column("id")]
	[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
	public Guid Id { get; set; }

	[Column("version")]
	public int Version { get; set; }

	[Column("deleted")]
	public bool Deleted { get; set; }

	[Column("active")]
	public bool? Active { get; set; }

	[Column("contactstring")]
	[StringLength(1000)]
	public string ContactDetails { get; set; }

	[Column("email")]
	[StringLength(200)]
	public string Email { get; set; }

	[Column("firstname")]
	[StringLength(200)]
	public string Firstname { get; set; }

	[Column("lastname")]
	[StringLength(200)]
	public string Lastname { get; set; }

	[Column("initials")]
	[StringLength(20)]
	public string Initials { get; set; }

	[Column("lastpasswordchange")]
	public DateTime? LastPasswordChange
	{
		get => UtcDateTimeHelper.UnspecifiedToUtcKind(lastPasswordChange);

		set => lastPasswordChange = UtcDateTimeHelper.ConvertToUtc(value);
	}

	[Column("name")]
	[StringLength(200)]
	public string Name
	{
		get
		{
			if (!IsGroup.HasValue || !IsGroup.Value)
			{
				name = $"{Firstname} {Lastname}";
			}

			return name;
		}

		set => name = value;
	}

	[Column("username")]
	[Required]
	[StringLength(20)]
	public string Username { get; set; }

	[Column("password")]
	[StringLength(128)]
	public string Password { get; set; }

	[Column("isgroup")]
	public bool? IsGroup { get; set; }

	[Column("externalid")]
	[StringLength(255)]
	public string ExternalId { get; set; }

	#endregion

	#region Navigation properties

	public virtual ICollection<Worklist> Worklists { get; set; }

	public virtual ICollection<Personalization> Personalizations { get; set; }

	public virtual ICollection<WorklistPermission> WorklistPermissions { get; set; }

	public virtual ICollection<Role> ActorRoles { get; set; }

	public virtual ICollection<Role> UnitRoles { get; set; }

	public virtual ICollection<Task> OwnedTasks { get; set; }

	public virtual ICollection<Task> SentTasks { get; set; }

	#endregion
}

migrationBuilder.CreateTable(
	name: "orgunit",
	columns: table => new
	{
		id = table.Column<Guid>(nullable: false, defaultValueSql: newGuidFunction),
		active = table.Column<bool>(nullable: true),
		contactstring = table.Column<string>(maxLength: 1000, nullable: true),
		created = table.Column<DateTime>(nullable: true),
		deleted = table.Column<bool>(nullable: false, defaultValue: false),
		email = table.Column<string>(maxLength: 200, nullable: true),
		externalid = table.Column<string>(maxLength: 255, nullable: true),
		firstname = table.Column<string>(maxLength: 200, nullable: true),
		initials = table.Column<string>(maxLength: 20, nullable: true),
		isgroup = table.Column<bool>(nullable: true),
		lastchanged = table.Column<DateTime>(nullable: true),
		lastchangedbyusername = table.Column<string>(maxLength: 20, nullable: true),
		lastpasswordchange = table.Column<DateTime>(nullable: true),
		lastname = table.Column<string>(maxLength: 200, nullable: true),
		name = table.Column<string>(maxLength: 200, nullable: true),
		password = table.Column<string>(maxLength: 128, nullable: true),
		username = table.Column<string>(maxLength: 20, nullable: false),
		version = table.Column<int>(nullable: false, defaultValue: 0)
	},
	constraints: table =>
	{
		table.PrimaryKey("PK_orgunit", x => x.id);
		table.UniqueConstraint("AK_dbo.orgunit.username", x => x.username);
	});

	
modelBuilder.Entity("Computas.FS.Models.OrgUnit", b =>
{
	b.Property<Guid>("Id")
		.ValueGeneratedOnAdd()
		.HasColumnName("id")
		.HasDefaultValueSql("newsequentialid()");

	b.Property<bool?>("Active")
		.HasColumnName("active");

	b.Property<string>("ContactDetails")
		.HasColumnName("contactstring")
		.HasMaxLength(1000);

	b.Property<DateTime?>("Created")
		.HasColumnName("created");

	b.Property<bool>("Deleted")
		.ValueGeneratedOnAdd()
		.HasColumnName("deleted")
		.HasDefaultValue(false);

	b.Property<string>("Email")
		.HasColumnName("email")
		.HasMaxLength(200);

	b.Property<string>("ExternalId")
		.HasColumnName("externalid")
		.HasMaxLength(255);

	b.Property<string>("Firstname")
		.HasColumnName("firstname")
		.HasMaxLength(200);

	b.Property<string>("Initials")
		.HasColumnName("initials")
		.HasMaxLength(20);

	b.Property<bool?>("IsGroup")
		.HasColumnName("isgroup");

	b.Property<DateTime?>("LastChanged")
		.HasColumnName("lastchanged");

	b.Property<string>("LastChangedByUsername")
		.HasColumnName("lastchangedbyusername")
		.HasMaxLength(20);

	b.Property<DateTime?>("LastPasswordChange")
		.HasColumnName("lastpasswordchange");

	b.Property<string>("Lastname")
		.HasColumnName("lastname")
		.HasMaxLength(200);

	b.Property<string>("Name")
		.HasColumnName("name")
		.HasMaxLength(200);

	b.Property<string>("Password")
		.HasColumnName("password")
		.HasMaxLength(128);

	b.Property<string>("Username")
		.IsRequired()
		.HasColumnName("username")
		.HasMaxLength(20);

	b.Property<int>("Version")
		.ValueGeneratedOnAdd()
		.HasColumnName("version")
		.HasDefaultValue(0);

	b.HasKey("Id");

	b.HasAlternateKey("Username")
		.HasName("AK_dbo.orgunit.username");

	b.HasIndex("ExternalId")
		.IsUnique()
		.HasName("IX_orgunit_externalid")
		.HasFilter("externalid IS NOT NULL");

	b.HasIndex("Username")
		.HasName("IX_orgunit_username");

	b.ToTable("orgunit");
});

modelBuilder.Entity<OrgUnit>().Property(ou => ou.Deleted).HasDefaultValue(false);
modelBuilder.Entity<OrgUnit>().Property(ou => ou.Version).HasDefaultValue(0);
modelBuilder.Entity<OrgUnit>().HasAlternateKey(ou => ou.Username).HasName("AK_dbo.orgunit.username");
modelBuilder.Entity<OrgUnit>().HasIndex(ou => ou.ExternalId).IsUnique().HasFilter("externalid IS NOT NULL").HasName("IX_orgunit_externalid");
modelBuilder.Entity<OrgUnit>().HasIndex(ou => ou.Username).HasName("IX_orgunit_username");
if (!IsInMemory)
{
	if (!UseMySql)
	{
		modelBuilder.Entity<OrgUnit>().Property(ou => ou.Id).HasDefaultValueSql("newsequentialid()");
	}
	else
	{
		modelBuilder.Entity<OrgUnit>().Property(ou => ou.Id).ValueGeneratedOnAdd();
	}
}

The same issue also applies to database first

I am getting the exact same error with.

I would recommend not to change the database.
When you make new migrations, your DB changes will be overwritten.

Please add some lines of code for each Boolean on the entity under the method 'OnModelCreating(ModelBuilder builder)' to show conversion of boolean to int(16)

builder.Entity<User>().Property(up => up.EmailConfirmed).HasConversion(new BoolToZeroOneConverter<Int16>());

Hello Cosmin Petrenciuc,

Thank you for the report.
Verified as described with VS 2019, C/NET 8.0.16 on Win10.

regards,
Umesh

Any ETA for the fix?

This workaround HasConversion(new BolTZerO Converter<Int16>()); it's working, but using scaffolding is cumbersome, because is necessary modify manually the code generated.

Placing this in the overridded DbContext.OnModelCreating method can mitigate this issue:

foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
    foreach (var property in entityType.GetProperties())
    {
        if (property.ClrType == typeof(bool) || property.ClrType == typeof(bool?))             
            property.SetValueConverter(new BoolToZeroOneConverter<Int16>());
    }
}

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.20 release, and here's the proposed changelog entry from the documentation team:

An attempt to read the record of a model class defined to correspond to a
MySQL table with a property of type bool? (nullable Boolean), using the EF
Core database context, returned an error message.

Thank you for the bug report.

Hi Christine,
 
You said "Fixed as of the upcoming MySQL Connector/NET 8.0.20 release", when is date to release next connector version ?