New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Practical Way to use MySql Enums #36
Comments
Hi @greghroberts, We don't have any plan for supporting enum column type yet. It seems that the official sqlserver provider for ef core doesn't support this too. So change this issue into backlog. |
Enum column types are specific to MySQL. I don't believe SqlServer has an equivalent yet. This feature should be pretty easy to do as it just needs awareness of the custom type similarly to what u are doing with JSON type. |
For reference it seems the npgsql is working through some of this too. npgsql/efcore.pg#27 I believe Postgres is even more complex since enums are defined as new types instead of a general enum type. |
Supporting |
MySqlConnector now has support for MySQL Server stores If you want to convert them "automagically" to |
@kagamine I will take a look at this one, we need it for our project. |
It appears that value conversions are being introduced in EF Core 2.1 which would allow storing enums as strings.
https://blogs.msdn.microsoft.com/dotnet/2018/02/02/entity-framework-core-2-1-roadmap/ |
Hi, |
|
Oh... have to wait for 7.0 😢 |
oh my god, maybe 8.0? |
By default, EF Core maps CLR However, you can simply setup an entity property to use a CLR Take a look at the following sample console app, that demonstrates this: Program.csusing System;
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; }
public Topping Topping { get; set; }
public Waffle Waffle { get; set; }
}
public enum Topping
{
None = 1, // <-- the start value MySQL uses for ENUM column values
ChocolateSprinkles,
}
public enum Waffle
{
Waffle, // <-- implicitly means `0` in EF Core, will be represented by `1` in MySQL ENUM columns
Sugar,
ChocolateDipped
}
public class Context : DbContext
{
public DbSet<IceCream> IceCreams { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=Issue36";
var serverVersion = ServerVersion.AutoDetect(connectionString);
optionsBuilder
.UseMySql(connectionString, serverVersion)
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<IceCream>(
entity =>
{
entity.Property(i => i.Topping)
.HasColumnType("enum('None', 'ChocolateSprinkles')")
.HasConversion<string>();
entity.Property(i => i.Waffle)
.HasColumnType($"enum('{string.Join("', '", Enum.GetNames<Waffle>())}')")
.HasConversion<string>();
entity.HasData(
new IceCream
{
IceCreamId = 1,
Name = "Vanilla",
Topping = Topping.None,
Waffle = Waffle.Waffle,
},
new IceCream
{
IceCreamId = 2,
Name = "Chocolate",
Topping = Topping.ChocolateSprinkles,
Waffle = Waffle.ChocolateDipped,
});
});
}
}
internal static class Program
{
private static void Main()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var expiringIceCreams = context.IceCreams
.Where(i => i.Topping == Topping.ChocolateSprinkles &&
i.Waffle == Waffle.ChocolateDipped)
.ToList();
Trace.Assert(expiringIceCreams.Count == 1);
Trace.Assert(expiringIceCreams[0].IceCreamId == 2);
}
}
} It generates the following Output (SQL)info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 6.0.0 initialized 'Context' using provider 'Pomelo.EntityFrameworkCore.MySql:6.0.0' with options: ServerVersion 8.0.25-mysql SensitiveDataLoggingEnabled DetailedErrorsEnabled
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE DATABASE `Issue36`;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER DATABASE CHARACTER SET utf8mb4;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (32ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE `IceCreams` (
`IceCreamId` int NOT NULL AUTO_INCREMENT,
`Name` longtext CHARACTER SET utf8mb4 NOT NULL,
`Topping` enum('None', 'ChocolateSprinkles') CHARACTER SET utf8mb4 NOT NULL,
`Waffle` enum('Waffle', 'Sugar', 'ChocolateDipped') CHARACTER SET utf8mb4 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`, `Name`, `Topping`, `Waffle`)
VALUES (1, 'Vanilla', 'None', 'Waffle');
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO `IceCreams` (`IceCreamId`, `Name`, `Topping`, `Waffle`)
VALUES (2, 'Chocolate', 'ChocolateSprinkles', 'ChocolateDipped');
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT `i`.`IceCreamId`, `i`.`Name`, `i`.`Topping`, `i`.`Waffle`
FROM `IceCreams` AS `i`
WHERE (`i`.`Topping` = 'ChocolateSprinkles') AND (`i`.`Waffle` = 'ChocolateDipped') As mentioned in the code, unless you let your CLR That is technically not an issue, because MySqlConnector and the converter translates this fine here, since the converter translation happens using the name of the CLR But if you care about using the same underlying integer value for CLR that is used by MySQL, then you want to let your CLR Database table
Of course you could alter the default conversion logic in any way you want, e.g. if your database |
@lauxjpn With MySQL version 5.7 it generates the migrations with a defaultValue='' for the mandatory enums. And there is no way to make it non-default. Migration: migrationBuilder.AddColumn<string>(
name: "Criticality",
table: "LubricationPlans",
type: "enum('Critical', 'SemiCritical', 'NonCritical')",
nullable: false,
defaultValue: "")
.Annotation("MySql:CharSet", "utf8mb4"); Config: var criticalityValues = Enum.GetValues(typeof(LubricationPlanCriticality))
.Cast<LubricationPlanCriticality>()
.Select(x => "'" + x + "'")
.ToList();
builder
.Property(lubricationPlan => lubricationPlan.Criticality)
.HasColumnType("ENUM(" + string.Join(",", criticalityValues) + ")")
.IsRequired()
.HasConversion(
v => v.ToString(),
v =>
(LubricationPlanCriticality)
Enum.Parse(typeof(LubricationPlanCriticality), v)
); |
If anyone want to use a convenient option to address this issue, here is a code snippet for that. public static class EnumUtils
{
public static string ToMySqlEnum<TEnum>()
where TEnum : Enum
{
var enumNames = Enum
.GetNames(typeof(TEnum))
.Select(enumValue => $"'{enumValue}'")
.ToArray();
var enumString = string.Join(", ", enumNames);
return $"enum({enumString})";
}
} In DbContext class: protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.ApplyConfigurationsFromAssembly(typeof(ApplicationDbContext).Assembly);
foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
var statusEnum = entityType.FindProperty("Status");
statusEnum?.SetColumnType(EnumUtils.ToMySqlEnum<Status>());
}
}
protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
configurationBuilder.Properties<Enum>().HaveConversion<string>();
} In application with separate configuration class: builder.Property(x => x.Status)
.HasColumnType(EnumUtils.ToMySqlEnum<Status>())
.HasConversion<string>(); The generated SQL in the migraton: status = table.Column<string>(type: "enum('Published', 'Pending', 'Draft')", nullable: false)
.Annotation("MySql:CharSet", "utf8mb4"), |
@BartoGabriel If a property is mandatory (which an non-nullable CLR You could also make your property nullable, which would result in a default value of |
@lauxjpn MySQL allows the creation of an enum type column that is mandatory and without specifying a default value. At the database level, I usually create it like this (it forces the user to specify it in the inserts): ALTER TABLE `test`
ADD COLUMN `enumColumn` ENUM('Y','N') NOT NULL; I believe that's why I expected the migration not to specify that default value, which also doesn't make sense since the string '' does not comply with any enum value. I understand about specifying HasDefaultValue... And I think the developer should consider, when using enums in this way, setting the HasDefaultValue equal to the default value that C# assigns to a non-nullable enum (I'm not sure if I explained myself correctly...). |
Pomelo doesn't have special support for MySQL
MySQL stores Once we properly support MySQL |
Steps to reproduce
Almost everything works with MySql enums when you change the column type with the following method that you call for every property in your context:
The issue
Persistence and migrations work fine here. The limitation is on querying as by default MySql will return a string for this column and EF blows up trying to convert that to an int. All that needs to be done for the simple scenario is to either pre-cast these columns to int when querying, and/or handling the string => enum conversion. I'm not sure which approach would be easier, but again, I'm not looking for full complete enum support, just enough hooks to make it usable.
I'm happy to help contribute, but would ask for recommendation on how to make one of these options work.
Further technical details
MySQL version: 5.7
Operating system: Ubuntu
Pomelo.EntityFrameworkCore.MySql version: 1.0
The text was updated successfully, but these errors were encountered: