Pomelo.EntityFrameworkCore.MySql: Reading a database value failed for tinyint(3) unsigned / byte

Steps to reproduce

    public partial class TestModel
    {
        public int Id { get; set; }
        public byte Value { get; set; }
    }

public class TestContext : DbContext
{

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TestModel>(entity =>
        {
            entity.Property(e => e.Id)
                .HasColumnName("id")
                .HasColumnType("int(11)")
                .HasDefaultValueSql("0");

        entity.Property(e => e.Value)
                .HasColumnName("value")
                .HasColumnType("tinyint(3) unsigned")
                .HasDefaultValueSql("1");
        });
    }
}

The issue

When trying to read the entity from the database I get the following exception:

Message: `An exception occurred while reading a database value for property 'TestModel.Value'. The expected type was 'System.SByte' but the actual value was of type 'System.Byte'.`
Stacktrace: `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.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
   at System.Linq.AsyncEnumerable.AsyncEnumerableAdapter`1.MoveNextCore(CancellationToken cancellationToken)
   at System.Linq.AsyncEnumerable.AsyncIterator`1.MoveNext(CancellationToken cancellationToken)
   at System.Linq.AsyncEnumerable.DefaultIfEmptyAsyncIterator`1.MoveNextCore(CancellationToken cancellationToken)
   at System.Linq.AsyncEnumerable.AsyncIterator`1.MoveNext(CancellationToken cancellationToken)
   at System.Linq.AsyncEnumerable.FirstOrDefault_[TSource](IAsyncEnumerable`1 source, CancellationToken cancellationToken)
   at my class

The value column in the database is marked as tinyint(3) unsigned and a row contains for example the Id 1 and Value 2.

If I change the column type in the database (not in the code) to just tinyint(3) it works.

Further technical details

MySQL version: 8.0.12 Operating system: Windows 10 Pomelo.EntityFrameworkCore.MySql version: 2.1.2

About this issue

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

Most upvoted comments

The problem is not MySqlConnector

When generating a migration with .HasColumnType("tinyint(3) unsigned") it is incorrectly generated as sbyte:

                    value = table.Column<sbyte>(type: "tinyint(3) unsigned", nullable: false, defaultValue: (sbyte)1)

As a workaround use this:

				entity.Property(e => e.Value)
					.HasColumnName("value")
					.HasColumnType("tinyint unsigned")
					.HasDefaultValue(1);

It properly generates the migration with byte:

                    value = table.Column<byte>(type: "tinyint unsigned", nullable: false, defaultValue: (byte)1)

@AndriySvyryd do you know why specifying the display length in HasColumnType would cause EF to lose track of signed/unsigned?

caleblloyd on Oct 18, 2018