Scaffold problem

[Wolfteam]

Steps to reproduce

I just created a simple database with two tables.

DROP DATABASE IF EXISTS horarios;
CREATE DATABASE horarios;
USE horarios;

CREATE TABLE tipo_aula_materia(
	id_tipo TINYINT UNSIGNED AUTO_INCREMENT,
	nombre_tipo VARCHAR(20) NOT NULL,
	PRIMARY KEY (id_tipo)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE carreras (
	id_carrera TINYINT UNSIGNED AUTO_INCREMENT,
	nombre_carrera VARCHAR(20) NOT NULL,
	PRIMARY KEY (id_carrera)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tipo_aula_materia (nombre_tipo) VALUES 
("Teoria"),
("Laboratorio");

INSERT INTO carreras (nombre_carrera) VALUES 
("Sistemas"),
("Mecanica"),
("Industrial");

Then i scaffold using vs 2017:

Scaffold-DbContext "server=localhost;userid=wolfteam20;pwd=123;port=3306;database=horarios;sslmode=none;" Pomelo.EntityFrameworkCore.MySql -OutputDir Models -f

Then i got

    public partial class Carreras
    {
        public sbyte IdCarrera { get; set; }
        public string NombreCarrera { get; set; }
    }
    public partial class TipoAulaMateria
    {
        public sbyte IdTipo { get; set; }
        public string NombreTipo { get; set; }
    }

    public partial class HorariosContext : DbContext
    {
        public virtual DbSet<Carreras> Carreras { get; set; }
        public virtual DbSet<TipoAulaMateria> TipoAulaMateria { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {                optionsBuilder.UseMySql("server=localhost;userid=wolfteam20;pwd=123;port=3306;database=horarios;sslmode=none;");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Carreras>(entity =>
            {
                entity.HasKey(e => e.IdCarrera);

                entity.ToTable("carreras");

                entity.Property(e => e.IdCarrera)
                    .HasColumnName("id_carrera")
                    .HasColumnType("tinyint(3) unsigned")
                    .ValueGeneratedNever();

                entity.Property(e => e.NombreCarrera)
                    .IsRequired()
                    .HasColumnName("nombre_carrera")
                    .HasMaxLength(20);
            });

            modelBuilder.Entity<TipoAulaMateria>(entity =>
            {
                entity.HasKey(e => e.IdTipo);

                entity.ToTable("tipo_aula_materia");

                entity.Property(e => e.IdTipo)
                    .HasColumnName("id_tipo")
                    .HasColumnType("tinyint(3) unsigned")
                    .ValueGeneratedNever();

                entity.Property(e => e.NombreTipo)
                    .IsRequired()
                    .HasColumnName("nombre_tipo")
                    .HasMaxLength(20);
            });
        }
    }

The issue

I tried a simple

            using (var db = new HorariosContext())
            {
                var query = from u in db.Carreras select u;
                foreach (var item in query)
                {
                    Debug.WriteLine(item.NombreCarrera);
                }

            }

But i got:

Exception message:
`System.InvalidOperationException: 'An exception occurred while reading a database value. The expected type was 'System.SByte' but the actual value was of type 'System.Byte'.'`

Workaround

I changed each property from sbyte to byte and added

        [Column(TypeName = "SMALLINT UNSIGNED")]
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]

Further technical details

MySQL version: 5.7.19
Operating system: Windows 10 32bits
Pomelo.EntityFrameworkCore.MySql version: 2.0.1-preview-10067

[mguinness]

Looking at the source I think I can see what's occurring here. Using SHOW COLUMNS that field is returned as TINYINT(3) UNSIGNED which is set to StoreType in GetColumns.

Internally all the mappings are stored in the _storeTypeMappings dictionary. However when this is referenced in CreateMappingFromStoreType it won't match.

The fallback in that function is it then truncates the string after the opening parentheses (meaning just TINYINT) which does match, but to signed tinyint (DbType.SByte).

As to the fix? I suppose it could either be some string manipulation before setting StoreType property or maybe a custom equality comparer for the dictionary. I guess I'd lean toward the former, but it could have unintended consequences.

[mguinness]

@Wolfteam Please try 10070 and see if a new scaffold generates the correct type.

[Wolfteam]

yep, seems to be fixed :D, thanks