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)
Counterintuitively I have actually had to set
TreatTinyAsBoolean=false
when using a mapping like this. If I don’t configure.HasColumnType("tinyint(1)")
then abool
model property gets set just fine when querying a column of typetinyint(1)
, but whenHasColumnType("tinyint(1)")
is configured I would consistently get the exception above unless I setTreatTinyAsBoolean=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.
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
I had exactly the same experience - wtf? Why does it need to be false?
The docs say:
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.