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:

            modelBuilder.Entity<Role>();
              .Property(c => c.Enabled)
               .HasConversion<int>();

my entity and use code:

    [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

[jbirby2]

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;

[MikeAlhayek]

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

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

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.

                     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

[david-d-an]

@CrestApps, your solution solution worked for me. My project is Core 2.2 and importing packages below

<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.2.6" PrivateAssets="All" />
<DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.3" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.2.6" />
<PackageReference Include="MySql.Data.EntityFrameworkCore" Version="8.0.19" />