Блог

Сегодня я переношу свой проект на .NET 6 с помощью базы данных MySQL. Я попробовал Помело.EntityFrameworkCore.MySQL впервые, но произошло несколько ошибок. Я исправил некоторые из них, но последний я не смог.

 System.InvalidOperationException: The property 'SqlClass.Disabled' is of type 'byte' which is not supported by the current database provider. Either change the property CLR type, or ignore the property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.   entity.Property(e =gt; e.Disabled)  //.HasConversionlt;intgt;()  .IsRequired()  .HasMaxLength(255)  .HasColumnName("disabled")  .HasColumnType("tinyint(1)");   [Required]  [MaxLength(255)]  public byte Disabled { get; set; }  

Есть какие-нибудь идеи ? Спасибо.

В основном есть 3 простых варианта, все они демонстрируются здесь вместе со IceCream.Available свойством:

1. Используйте System.Boolean вместо System.Byte

tinyint(1) System.Boolean По умолчанию Pomelo переводится как «Помело». Поэтому , если вы измените тип CLR вашей собственности с byte на bool , это сработает «из коробки»:

Программа.cs

 using System.ComponentModel.DataAnnotations; using System.Diagnostics; using System.Linq; using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.Logging;  namespace IssueConsoleTemplate {  public class IceCream  {  public int IceCreamId { get; set; }  public string Name { get; set; }    [Required]  [Column(TypeName = "tinyint(1)")] // lt;-- redundant (bool is translated to tinyint(1) by default)  public bool Available { get; set; } // lt;-- use bool  }   public class Context : DbContext  {  public DbSetlt;IceCreamgt; IceCreams { get; set; }   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  {  var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=So70198786";  var serverVersion = ServerVersion.AutoDetect(connectionString);   optionsBuilder  .UseMySql(connectionString, serverVersion)  .UseLoggerFactory(  LoggerFactory.Create(  b =gt; b  .AddConsole()  .AddFilter(level =gt; level gt;= LogLevel.Information)))  .EnableSensitiveDataLogging()  .EnableDetailedErrors();  }   protected override void OnModelCreating(ModelBuilder modelBuilder)  {  modelBuilder.Entitylt;IceCreamgt;(  entity =gt;  {  // Not needed if you are using data annotations:  //  // entity.Property(e =gt; e.Available)  // .IsRequired()  // .HasColumnType("tinyint(1)"); // lt;-- redundant (bool is translated to tinyint(1) by default)   entity.HasData(  new IceCream  {  IceCreamId = 1,  Name = "Vanilla",  Available = true, // lt;-- bool  },  new IceCream  {  IceCreamId = 2,  Name = "Chocolate",  Available = false, // lt;-- bool  });  });  }  }   internal static class Program  {  private static void Main()  {  using var context = new Context();   context.Database.EnsureDeleted();  context.Database.EnsureCreated();   var availableIceCreams = context.IceCreams  .Where(i =gt; i.Available) // lt;-- bool  .ToList();    Trace.Assert(availableIceCreams.Count == 1);  Trace.Assert(availableIceCreams[0].Name == "Vanilla");  }  } }  

Вывод (SQL)

 warn: Microsoft.EntityFrameworkCore.Model.Validation[10400]  Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development. info: Microsoft.EntityFrameworkCore.Infrastructure[10403]  Entity Framework Core 6.0.0 initialized 'Context' using provider 'Pomelo.EntityFrameworkCore.MySql:6.0.0-rtm.1' with options: ServerVersion 8.0.25-mysql SensitiveDataLoggingEnabled DetailedErrorsEnabled info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (38ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  DROP DATABASE `So70198786`; info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  CREATE DATABASE `So70198786`; info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (15ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  ALTER DATABASE CHARACTER SET utf8mb4; info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (47ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  CREATE TABLE `IceCreams` (  `IceCreamId` int NOT NULL AUTO_INCREMENT,  `Name` longtext CHARACTER SET utf8mb4 NULL,  `Available` tinyint(1) NOT NULL,  CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)  ) CHARACTER SET=utf8mb4; info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  INSERT INTO `IceCreams` (`IceCreamId`, `Available`, `Name`)  VALUES (1, TRUE, 'Vanilla'); info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  INSERT INTO `IceCreams` (`IceCreamId`, `Available`, `Name`)  VALUES (2, FALSE, 'Chocolate'); info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  SELECT `i`.`IceCreamId`, `i`.`Available`, `i`.`Name`  FROM `IceCreams` AS `i`  WHERE `i`.`Available`  

2. Используйте tinyint вместо tinyint(1)

В случае, если вы определенно хотите использовать System.Byte в качестве типа CLR свою собственность, используйте tinyint вместо tinyint(1) . Все tinyint , кроме tinyint(1) System.Byte , по умолчанию переведены в:

Программа.cs

 using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Diagnostics; using System.Linq; using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.Logging;  namespace IssueConsoleTemplate {  public class IceCream  {  public int IceCreamId { get; set; }  public string Name { get; set; }    [Required]  [Column(TypeName = "tinyint")] // lt;-- redundant (byte is translated to tinyint by default)  public byte Available { get; set; }  }   public class Context : DbContext  {  public DbSetlt;IceCreamgt; IceCreams { get; set; }   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  {  var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=So70198786_01";  var serverVersion = ServerVersion.AutoDetect(connectionString);   optionsBuilder  .UseMySql(connectionString, serverVersion)  .UseLoggerFactory(  LoggerFactory.Create(  b =gt; b  .AddConsole()  .AddFilter(level =gt; level gt;= LogLevel.Information)))  .EnableSensitiveDataLogging()  .EnableDetailedErrors();  }   protected override void OnModelCreating(ModelBuilder modelBuilder)  {  modelBuilder.Entitylt;IceCreamgt;(  entity =gt;  {  // Not needed if you are using data annotations:  //  // entity.Property(e =gt; e.Available)  // .IsRequired()  // .HasColumnType("tinyint"); // lt;-- redundant (byte is translated to tinyint by default)   entity.HasData(  new IceCream  {  IceCreamId = 1,  Name = "Vanilla",  Available = 1, // lt;-- byte  },  new IceCream  {  IceCreamId = 2,  Name = "Chocolate",  Available = 0, // lt;-- byte  });  });  }  }   internal static class Program  {  private static void Main()  {  using var context = new Context();   context.Database.EnsureDeleted();  context.Database.EnsureCreated();   var availableIceCreams = context.IceCreams  .Where(i =gt; i.Available != 0) // lt;-- byte  .ToList();    Trace.Assert(availableIceCreams.Count == 1);  Trace.Assert(availableIceCreams[0].Name == "Vanilla");  }  } }  

Вывод (SQL)

 warn: Microsoft.EntityFrameworkCore.Model.Validation[10400]  Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development. info: Microsoft.EntityFrameworkCore.Infrastructure[10403]  Entity Framework Core 6.0.0 initialized 'Context' using provider 'Pomelo.EntityFrameworkCore.MySql:6.0.0-rtm.1' with options: ServerVersion 8.0.25-mysql SensitiveDataLoggingEnabled DetailedErrorsEnabled info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (42ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  DROP DATABASE `So70198786_01`; info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (41ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  CREATE DATABASE `So70198786_01`; info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  ALTER DATABASE CHARACTER SET utf8mb4; info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (48ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  CREATE TABLE `IceCreams` (  `IceCreamId` int NOT NULL AUTO_INCREMENT,  `Name` longtext CHARACTER SET utf8mb4 NULL,  `Available` tinyint NOT NULL,  CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)  ) CHARACTER SET=utf8mb4; info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  INSERT INTO `IceCreams` (`IceCreamId`, `Available`, `Name`)  VALUES (1, 1, 'Vanilla'); info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  INSERT INTO `IceCreams` (`IceCreamId`, `Available`, `Name`)  VALUES (2, 0, 'Chocolate'); info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  SELECT `i`.`IceCreamId`, `i`.`Available`, `i`.`Name`  FROM `IceCreams` AS `i`  WHERE `i`.`Available` lt;gt; 0  

3. Измените System.Boolean сопоставление по умолчанию на bit(1) или удалите его полностью

Наконец, если вам нужно точно сопоставить tinyint(1) System.Byte , вы можете изменить сопоставление по умолчанию, для которого используется System.Boolean Pomelo, установив DbContext опцию в своем UseMySql() вызове:

Программа.cs

 using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Diagnostics; using System.Linq; using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.Logging; using Pomelo.EntityFrameworkCore.MySql.Infrastructure;  namespace IssueConsoleTemplate {  public class IceCream  {  public int IceCreamId { get; set; }  public string Name { get; set; }    [Required]  [Column(TypeName = "tinyint(1)")] // lt;-- necessary (otherwise gets translated to tinyint)  public byte Available { get; set; }  }   public class Context : DbContext  {  public DbSetlt;IceCreamgt; IceCreams { get; set; }   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  {  var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=So70198786_02";  var serverVersion = ServerVersion.AutoDetect(connectionString);   optionsBuilder  .UseMySql(  connectionString,  serverVersion,  options =gt; options.DefaultDataTypeMappings( // lt;-- change default data type mappings  m =gt; m.WithClrBoolean(MySqlBooleanType.Bit1))) // lt;-- or even MySqlBooleanType.None  .UseLoggerFactory(  LoggerFactory.Create(  b =gt; b  .AddConsole()  .AddFilter(level =gt; level gt;= LogLevel.Information)))  .EnableSensitiveDataLogging()  .EnableDetailedErrors();  }   protected override void OnModelCreating(ModelBuilder modelBuilder)  {  modelBuilder.Entitylt;IceCreamgt;(  entity =gt;  {  // Not needed if you are using data annotations:  //  // entity.Property(e =gt; e.Available)  // .IsRequired()  // .HasColumnType("tinyint(1)"); // lt;-- necessary (otherwise gets translated to tinyint)   entity.HasData(  new IceCream  {  IceCreamId = 1,  Name = "Vanilla",  Available = 1, // lt;-- byte  },  new IceCream  {  IceCreamId = 2,  Name = "Chocolate",  Available = 0, // lt;-- byte  });  });  }  }   internal static class Program  {  private static void Main()  {  using var context = new Context();   context.Database.EnsureDeleted();  context.Database.EnsureCreated();   var availableIceCreams = context.IceCreams  .Where(i =gt; i.Available != 0) // lt;-- byte  .ToList();    Trace.Assert(availableIceCreams.Count == 1);  Trace.Assert(availableIceCreams[0].Name == "Vanilla");  }  } }  

Output (SQL)

 warn: Microsoft.EntityFrameworkCore.Model.Validation[10400]  Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development. info: Microsoft.EntityFrameworkCore.Infrastructure[10403]  Entity Framework Core 6.0.0 initialized 'Context' using provider 'Pomelo.EntityFrameworkCore.MySql:6.0.0-rtm.1' with options: ServerVersion 8.0.25-mysql SensitiveDataLoggingEnabled DetailedErrorsEnabled info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (40ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  DROP DATABASE `So70198786_02`; info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  CREATE DATABASE `So70198786_02`; info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  ALTER DATABASE CHARACTER SET utf8mb4; info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (86ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  CREATE TABLE `IceCreams` (  `IceCreamId` int NOT NULL AUTO_INCREMENT,  `Name` longtext CHARACTER SET utf8mb4 NULL,  `Available` tinyint(1) NOT NULL,  CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)  ) CHARACTER SET=utf8mb4; info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  INSERT INTO `IceCreams` (`IceCreamId`, `Available`, `Name`)  VALUES (1, 1, 'Vanilla'); info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  INSERT INTO `IceCreams` (`IceCreamId`, `Available`, `Name`)  VALUES (2, 0, 'Chocolate'); info: Microsoft.EntityFrameworkCore.Database.Command[20101]  Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']  SELECT `i`.`IceCreamId`, `i`.`Available`, `i`.`Name`  FROM `IceCreams` AS `i`  WHERE `i`.`Available` lt;gt; 0  

Не имеет отношения к теме этого вопроса [MaxLength(255)] или .HasMaxLength(255) не влияет на нестроковые столбцы.