Pomelo.entityframeworkcore.mysql: Unable to cast object of type 'System.Boolean' to type 'System.Byte'

[enchev]

Steps to reproduce

Hey folks,

I'm trying to map the relationship between Customer and Store in Sakila Sample Database however I'm receiving invalid cast exception. store_id is tinyint(3) in both Customer and Store tables.

The code:

Customer.cs

namespace MyApp.Models.Sakila
{
  [Table("customer")]
  public class Customer
  {
    [Key]
    public Int16? customer_id
    {
      get;
      set;
    }

   ...

    public Byte? store_id
    {
      get;
      set;
    }

    [ForeignKey("store_id")]
    public Store Store { get; set; }
  }
}

Store.cs

namespace MyApp.Models.Sakila
{
  [Table("store")]
  public class Store
  {
    [Key]
    public Byte? store_id
    {
      get;
      set;
    }

   ...

    [InverseProperty("Store")]
    public ICollection<Customer> Customers { get; set; }
  }
}

The issue

Exception message: Unable to cast object of type 'System.Boolean' to type 'System.Byte'
Stack trace:
 at MySql.Data.MySqlClient.MySqlDataReader.GetByte(Int32 ordinal)
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.MySqlQueryingEnumerable`1.MySqlEnumerator.MoveNext()
   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__15`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSet(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInline(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext)
   at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObject(Object graph, Type type, ODataMessageWriter messageWriter, ODataSerializerContext writeContext)
   at Microsoft.AspNetCore.OData.Formatter.ODataOutputFormatter.WriteResponseBody(OutputFormatterWriteContext context)
   at Microsoft.AspNetCore.OData.Formatter.ODataOutputFormatter.<>c__DisplayClass3_0.<WriteResponseBodyAsync>b__0()
   at System.Threading.Tasks.Task.Execute()

Further technical details

MySQL version:
'innodb_version', '5.7.18'
'protocol_version', '10'
'slave_type_conversions', ''
'tls_version', 'TLSv1,TLSv1.1'
'version', '5.7.18-log'
'version_comment', 'MySQL Community Server (GPL)'
'version_compile_machine', 'x86_64'
'version_compile_os', 'Win64'

Operating system: Windows 10
Pomelo.EntityFrameworkCore.MySql version: 1.1.0

[caleblloyd]

Try setting TreatTinyAsBoolean=false in your connection string

https://mysql-net.github.io/MySqlConnector/connection-options/

[enchev]

Thanks @caleblloyd!

The exception is different now:

System.InvalidCastException: Unable to cast object of type 'System.SByte' to type 'System.Byte'.
   at MySql.Data.MySqlClient.MySqlDataReader.GetByte(Int32 ordinal)
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.MySqlQueryingEnumerable`1.MySqlEnumerator.MoveNext()
   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()

If I change store_id property type in both Customer and Store classes to SByte the result will be another exception:

System.InvalidCastException: Unable to cast object of type 'System.Byte' to type 'System.SByte'.
   at Microsoft.EntityFrameworkCore.Storage.Internal.WrappedMySqlDataReader.ConvertWithReflection[T](Int32 ordinal, InvalidCastException e)
   at Microsoft.EntityFrameworkCore.Storage.Internal.WrappedMySqlDataReader.GetFieldValue[T](Int32 ordinal)
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.MySqlQueryingEnumerable`1.MySqlEnumerator.MoveNext()
   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()

[caleblloyd]

That's because tinyint(3) is a sbyte if it's signed (-128 to 127). Change your CLR type in your model to sbyte.

If what you want is an unsigned byte (0 to 255), change your MySQL type to tinyint(3) unsigned

[caleblloyd]

It may be helpful for you to run scaffolding to do the initial setup of all of your models, then you can add more features to them after all of the proper types have been mapped.

[franva]

@caleblloyd saver ~! for the scaffolding~!!! I was using the MySql provider which caused the problem. After saw this scaffolding, I then realized I used the wrong one ~!!! Stuck here for 4 hours~!!!!