Efcore: No coercion operator is defined between types 'System.Int16' and 'System.Boolean' EF Core 2.1.4

[laball]

While I use EF Core 2.1.4 with MySQL, I will got an error while use bool field in entity,

Exception message:No coercion operator is defined between types 'System.Int16' and 'System.Boolean'
Stack trace:
Abp.AspNetCore.Mvc.ExceptionHandling.AbpExceptionFilter: 20:05:58 523 ERROR Thread：50 No coercion operator is defined between types 'System.Int16' and 'System.Boolean'. 
System.InvalidOperationException: No coercion operator is defined between types 'System.Int16' and 'System.Boolean'.
   at System.Linq.Expressions.Expression.GetUserDefinedCoercionOrThrow(ExpressionType coercionType, Expression expression, Type convertToType)
   at System.Linq.Expressions.Expression.Convert(Expression expression, Type type, MethodInfo method)
   at Microsoft.EntityFrameworkCore.Storage.TypedRelationalValueBufferFactoryFactory.CreateGetValueExpression(Expression dataReaderExpression, Expression indexExpression, TypeMaterializationInfo materializationInfo, Boolean box)
   at System.Linq.Enumerable.<SelectIterator>d__154`2.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Dynamic.Utils.CollectionExtensions.ToReadOnly[T](IEnumerable`1 enumerable)
   at System.Linq.Expressions.Expression.NewArrayInit(Type type, IEnumerable`1 initializers)
   at Microsoft.EntityFrameworkCore.Storage.TypedRelationalValueBufferFactoryFactory.CreateArrayInitializer(CacheKey cacheKey)
   at Microsoft.EntityFrameworkCore.Storage.TypedRelationalValueBufferFactoryFactory.<Create>b__10_0(CacheKey k)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
   at Microsoft.EntityFrameworkCore.Internal.NonCapturingLazyInitializer.EnsureInitialized[TParam,TValue](TValue& target, TParam param, Func`2 valueFactory)
   at Microsoft.EntityFrameworkCore.Query.Internal.ShaperCommandContext.NotifyReaderCreated(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Castle.Proxies.Invocations.IRepository`2_GetAllList_2.InvokeMethodOnTarget()
   at Castle.DynamicProxy.AbstractInvocation.Proceed()
   at Abp.Domain.Uow.UnitOfWorkInterceptor.PerformSyncUow(IInvocation invocation, UnitOfWorkOptions options) in D:\Github\aspnetboilerplate\src\Abp\Domain\Uow\UnitOfWorkInterceptor.cs:line 68
   at Castle.DynamicProxy.AbstractInvocation.Proceed()
   at Castle.Proxies.IRepository`1Proxy_1.GetAllList()
   at Lee.Abp.Web.Controllers.UserController.<Append>d__5.MoveNext() in E:\code\Demo\ConsoleApp1\Lee.Abp.Web\Controllers\UserController.cs:line 72
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at lambda_method(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.AwaitableObjectResultExecutor.<Execute>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__12.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeNextActionFilterAsync>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeInnerFilterAsync>d__13.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.<InvokeNextExceptionFilterAsync>d__24.MoveNext()

Steps to reproduce

I was use EF Core with ABP framework,while I use bool filed in my entity, there always got a error
"No coercion operator is defined between types 'System.Int16' and 'System.Boolean'"

But,when I use some code like this ,it gone:
```C#
modelBuilder.Entity();
.Property(c => c.Enabled)
.HasConversion();

my entity and use code:
```c#

    [Table("Lee_Role")]
    public class Role : BaseEntity
    {
        // type in db: bit(1) NOT NULL 
        public bool Enabled { get; set; }
    }

    var roles =  dbContext.Roles.ToList();

Further technical details

EF Core version: 2.1.4
Database Provider:
MySql.Data Version 8.0.13
MySql.Data.EntityFrameworkCore Version 8.0.13
Operating system:
Windows 7 professional

[rexhong19871011]

Hi,

Is any update for this? I got same one.

Thanks

[Alex-Schelkov]

Hi,
Yes I have the same problem.

[ajcvickers]

@laball This looks like an issue with the MySQL provider; please file an issue with them. Also, you might want to try the Pomelo open-source provider--some people have had more luck with it than with the official provider.

[karac38]

I had the same issue and you can try the Pomelo as was suggested by @ajcvickers or you can use value conversions:
entity.Property(o => o.[PropertyName]).HasConversion<int<a>>();

I had problems with some DSets for my custom tables as well as with the IdentityUser.

builder.Entity<ApplicationUser>(i => { i.Property(o => o.EmailConfirmed).HasConversion<int>(); i.Property(o => o.LockoutEnabled).HasConversion<int>(); i.Property(o => o.PhoneNumberConfirmed).HasConversion<int>(); i.Property(o => o.TwoFactorEnabled).HasConversion<int>(); });

Note: ApplicationUser is inherited from the IdentityUser

For me the biggest problem with Pomelo provider is that it does not work very well with async methods

[bigvigg]

I had the same problem. Also had other unhandled exceptions about converting between Int16 and Boolean whenever I used any bool property on my code-first models. I swapped from MySql.Data.EntityFrameworkCore to Pomelo.EntityFrameworkCore.MySql and now everything works fine.

[laball]

I fixed it by add a Converter like this:

using System;
using JetBrains.Annotations;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;

namespace Laball.Core.Common
{
    public class BoolToIntConverter : ValueConverter<bool, int>
    {
        public BoolToIntConverter([CanBeNull] ConverterMappingHints mappingHints = null)
            : base(
                  v => Convert.ToInt32(v),
                  v => Convert.ToBoolean(v),
                  mappingHints)
        {
        }

        public static ValueConverterInfo DefaultInfo { get; }
            = new ValueConverterInfo(typeof(bool), typeof(int), i => new BoolToIntConverter(i.MappingHints));
    }
}

and add it to the DbContext like this:

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            foreach (var entityType in builder.Model.GetEntityTypes())
            {
                foreach (var property in entityType.GetProperties())
                {
                    if (property.ClrType == typeof(bool))
                    {
                        property.SetValueConverter(new BoolToIntConverter());
                    }
                }
            }
        }

and it works fine.
I forgot to post here.

@rexhong19871011 @Alex-Schelkov @ajcvickers @karac38 @bigvigg

[ceshdelgado]

EF Core 2.1 has Built-in converters: for example BoolToZeroOneConverter

It works for me:

protected override void OnModelCreating(ModelBuilder builder)
{
       base.OnModelCreating(builder);
       builder.Entity< Role >()
            .Property(r => r.Enabled)
            .HasConversion(new BoolToZeroOneConverter<Int16>());
}

Provider: MySql.Data.EntityFrameworkCore

[tet-web-dev]

@ceshdelgado I placed your sample code in my OnModelCreating but "....Entity<Role>" is throwing an assembly reference error .

Im using EF 2.1.8

Note BoolToZeroOneConverter requires: using Microsoft.EntityFrameworkCore.Storage.ValueConversion;

[CrestApps]

I added the following code which automatically applies the BoolToZeroOneConverter<short> to every boolean property without having to do it manually for each property

Here is the DbContext class, to show how I am overriding the OnModelCreating
```C#
public class ApplicationDbContext : DbContext
{

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    // Iterate over every DbSet<> found in the current DbContext
    foreach (IMutableEntityType entityType in modelBuilder.Model.GetEntityTypes())
    {
        // Iterate over each property found on the Entity class
        foreach (IMutableProperty property in entityType.GetProperties())
        {
            if (property.PropertyInfo == null)
            {
                continue;
            }

            if (property.IsPrimaryKey() && IsPrimaryKey(property.PropertyInfo))
            {
                // At this point we know that the property is a primary key
               // let's set it to AutoIncrement on insert.
                modelBuilder.Entity(entityType.ClrType)
                            .Property(property.Name)
                            .ValueGeneratedOnAdd()
                            .Metadata.BeforeSaveBehavior = PropertySaveBehavior.Ignore;
            }
            else if (property.PropertyInfo.PropertyType.IsBoolean())
            {
                // Since MySQL stores bool as tinyint, let's add a converter so the tinyint is treated as boolean
                modelBuilder.Entity(entityType.ClrType)
                            .Property(property.Name)
                            .HasConversion(new BoolToZeroOneConverter<short>());
            }
        }

    };
}

private static bool IsPrimaryKey(PropertyInfo property)
{
    var identityTypes = new List<Type> {
        typeof(short),
        typeof(int),
        typeof(long)
    };

    return property.Name.Equals("Id", StringComparison.CurrentCultureIgnoreCase) && identityTypes.Contains(property.PropertyType);
}

}

Here are the type extensions
```C#
public static class TypeExtensions
{
    public static bool IsBoolean(this Type type)
    {
        Type t = Nullable.GetUnderlyingType(type) ?? type;

        return t == typeof(bool);
    }

    public static bool IsTrueEnum(this Type type)
    {
        Type t = Nullable.GetUnderlyingType(type) ?? type;

        return t.IsEnum;
    }
}

[je3667]

If others still run into this still just set the TypeName to bit on the Column attribute to your entity.

Example:
[Column(TypeName = "bit")] public Nullable<bool> MyColumn { get; set; }

[TiagoBrazSantos]

I still have problems with this same error, but it occurs when using .Select() when querying with a bool in a navigation object.

c# Context.Users.Where(x => x.Id == 10) .Select(s => new UserVM { Name = s.Name TestVM = new TestVM { Active = s.TestNavigation.Active } }) .FirstOrDefault();

EF Core version: 2.2.6
Database Provider:
MySql.Data Version 8.0.17
MySql.Data.EntityFrameworkCore Version 8.0.17