Pomelo.EntityFrameworkCore.MySql: Can't read TINYINT(1) as bool

Steps to reproduce

using System.Threading.Tasks;
using FluentAssertions;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Console;
using NUnit.Framework;

namespace Tests
{
    [TestFixture, Explicit]
    class MySqlCustomModelTests
    {
        static readonly LoggerFactory _loggerFactory
            = new LoggerFactory(new[] { new ConsoleLoggerProvider((category, logLevel) => true, false) });
        DbContextOptions<Context> _options;

        Context GetContext()
        {
            return new Context(_options);
        }

        class Context : DbContext
        {
            public Context(DbContextOptions<Context> options) : base(options) { }

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

                modelBuilder.Entity<Bool>(entity =>
                {
                    entity.ToTable("bools");
                    entity.Property(e => e.BoolValue).HasColumnType("tinyint(1)");
                });
            }

            public DbSet<Bool> Bools { get; set; }
        }

        class Bool
        {
            public int Id { get; set; }
            public bool BoolValue { get; set; }
        }

        [SetUp]
        protected void BeforeEach()
        {
            _options = new DbContextOptionsBuilder<Context>()
                .UseLoggerFactory(_loggerFactory)
                .UseMySql("Server=localhost;database=createtest;uid=user;pwd=password")
                .ConfigureWarnings(warnings => warnings.Throw(CoreEventId.IncludeIgnoredWarning))
                .Options;

            using (var db = GetContext())
            {
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();
            }
        }

        [Test]
        public async Task CanReadBools()
        {
            using (var db = GetContext())
            {
                db.Bools.Add(new Bool { BoolValue = false });
                db.Bools.Add(new Bool { BoolValue = true });
                await db.SaveChangesAsync();
            }

            using (var db = GetContext())
            {
                var list = await db.Bools.ToListAsync();
                list[0].BoolValue.Should().BeFalse();
                list[1].BoolValue.Should().BeTrue();
            }
        }
    }
}

The issue

Can’t read TINYINT(1) mapped to bool It’s not MySqlConnector issue, it reads fine with reader.GetBoolean(1)

Exception: 
System.InvalidOperationException : An exception occurred while reading a database value for property 'Bool.BoolValue'. The expected type was 'System.Boolean' but the actual value was of type 'System.Boolean'.
  ----> System.InvalidCastException : Unable to cast object of type 'System.Boolean' to type 'System.SByte'.
   at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityMaterializerSource.ThrowReadValueException[TValue](Exception exception, Object value, IPropertyBase property)
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken)
   at System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator`2.MoveNextCore(CancellationToken cancellationToken)
   at System.Linq.AsyncEnumerable.AsyncIterator`1.MoveNext(CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)
   at System.Linq.AsyncEnumerable.Aggregate_[TSource,TAccumulate,TResult](IAsyncEnumerable`1 source, TAccumulate seed, Func`3 accumulator, Func`2 resultSelector, CancellationToken cancellationToken)
   at Tests.MySqlCustomModelTests.CanReadBools() in C:\Dev\Tests\MySqlCustomModelTests.cs:line 75
   at NUnit.Framework.Internal.AsyncInvocationRegion.AsyncTaskInvocationRegion.WaitForPendingOperationsToComplete(Object invocationResult) in C:\src\nunit\nunit\src\NUnitFramework\framework\Internal\AsyncInvocationRegion.cs:line 113
   at NUnit.Framework.Internal.Commands.TestMethodCommand.RunAsyncTestMethod(TestExecutionContext context) in C:\src\nunit\nunit\src\NUnitFramework\framework\Internal\Commands\TestMethodCommand.cs:line 96
--InvalidCastException
   at MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue[T](Int32 ordinal) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 286
   at lambda_method(Closure , DbDataReader )

Further technical details

MySQL version: 8.0.11 Operating system: Win 7 Pomelo.EntityFrameworkCore.MySql version: 2.1.1

About this issue

  • Original URL
  • State: closed
  • Created 6 years ago
  • Comments: 15 (1 by maintainers)

Most upvoted comments

Counterintuitively I have actually had to set TreatTinyAsBoolean=false when using a mapping like this. If I don’t configure .HasColumnType("tinyint(1)") then a bool model property gets set just fine when querying a column of type tinyint(1), but when HasColumnType("tinyint(1)") is configured I would consistently get the exception above unless I set TreatTinyAsBoolean=false. I also tried the BoolToZeroOneConverter but it had the same issue.

Thanks @AccessViolator and everybody else for taking the time to report and discuss this issue. This has been fixed in #875.

Counterintuitively I have actually had to set TreatTinyAsBoolean=false when using a mapping like this. If I don’t configure .HasColumnType("tinyint(1)") then a bool model property gets set just fine when querying a column of type tinyint(1), but when HasColumnType("tinyint(1)") is configured I would consistently get the exception above unless I set TreatTinyAsBoolean=false. I also tried the BoolToZeroOneConverter but it had the same issue.

I had exactly the same experience - wtf? Why does it need to be false?

The docs say:

When set to true, TINYINT(1) values are returned as booleans. Setting this to false causes TINYINT(1) to be returned as sbyte/byte.

So I guess Pomelo needs to get back an sbyte/byte and can’t handle a bool?

Exactly the same experience. Setting it to false just doesn’t make sense to me but it seems to work. I hope that I don’t experience other issues with this later down the line.

I have a bool type property, .HasColumnType(“TINYINT(1)”) on all of my bool properties and then have TreatTinyAsBoolean=false in my connection string.

The above setup seems to work for scaffolding and reading the data out afterwards.

Weird

Counterintuitively I have actually had to set TreatTinyAsBoolean=false when using a mapping like this. If I don’t configure .HasColumnType("tinyint(1)") then a bool model property gets set just fine when querying a column of type tinyint(1), but when HasColumnType("tinyint(1)") is configured I would consistently get the exception above unless I set TreatTinyAsBoolean=false. I also tried the BoolToZeroOneConverter but it had the same issue.

I had exactly the same experience - wtf? Why does it need to be false?

The docs say:

When set to true, TINYINT(1) values are returned as booleans. Setting this to false causes TINYINT(1) to be returned as sbyte/byte.

So I guess Pomelo needs to get back an sbyte/byte and can’t handle a bool?

@twsl Is the field nullable in database? If so try bool? instead.