Comments (17)
I will implement this as our first new feature for 5.0.0
.
from pomelo.entityframeworkcore.mysql.
oh my god, maybe 8.0?
from pomelo.entityframeworkcore.mysql.
It appears that value conversions are being introduced in EF Core 2.1 which would allow storing enums as strings.
Starting with EF Core 2.1, value conversions can be applied to transform the values obtained from columns before they are applied to properties, and vice versa.
https://blogs.msdn.microsoft.com/dotnet/2018/02/02/entity-framework-core-2-1-roadmap/
from pomelo.entityframeworkcore.mysql.
Pomelo doesn't have special support for MySQL enum
values. They are currently just handled as any other CLR string
. And a non-nullable string
has a default value of ""
(empty string). It has no concept of what an enum
column type is. So if you want to use a CLR string
as the representation of a MySQL enum
, you need to specify the default value manually.
ALTER TABLE `test` ADD COLUMN `enumColumn` ENUM('Y','N') NOT NULL;
MySQL stores enum
columns internally as integer values starting with 1
. So your ALTER TABLE
statement is implicitly using a default value of 1
. See my earlier comment for more background on that.
Once we properly support MySQL enum
values, we would also correctly control the default value.
from pomelo.entityframeworkcore.mysql.
Hi,
we where hoping to use this feature with ef core and Pomelo.EntityFrameworkCore.MySql. Is this working? or planned?
from pomelo.entityframeworkcore.mysql.
@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...).
from pomelo.entityframeworkcore.mysql.
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.
from pomelo.entityframeworkcore.mysql.
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.
from pomelo.entityframeworkcore.mysql.
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.
from pomelo.entityframeworkcore.mysql.
Supporting ENUM
type is still on the backlog for the ADO.NET driver we are switching to in 1.0.1.
https://github.com/bgrainger/MySqlConnector/issues/4
from pomelo.entityframeworkcore.mysql.
MySqlConnector now has support for ENUM
columns in 0.7.3.
MySQL Server stores ENUM
columns as integers but sends them to/from clients as strings. So MySqlConnector will deserialize them as strings and MySqlDataReader.GetValue(i)
will return a string
.
If you want to convert them "automagically" to System.Enum
values, you will need to detect when GetDataTypeName
returns "ENUM"
, know the type of .NET enum
you wish to convert to, and call Enum.Parse(YourEnumType, reader.GetString(n), ignoreCase: true)
. The MySqlConnector library will not do this automatically.
from pomelo.entityframeworkcore.mysql.
@kagamine I will take a look at this one, we need it for our project.
from pomelo.entityframeworkcore.mysql.
Oh... have to wait for 7.0 😢
from pomelo.entityframeworkcore.mysql.
By default, EF Core maps CLR enum
type values to CLR Int32
before they get stored in the database table.
So without any Fluent API setup, using a CLR enum
works but does not map to a store type of ENUM
but of INT
.
However, you can simply setup an entity property to use a CLR enum
type and a ENUM
database store type.
Take a look at the following sample console app, that demonstrates this:
Program.cs
using 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 CREATE TABLE
statement, that includes the ENUM
column type:
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 enum
start with 1
, it will start with 0
. However, MySQL ENUM
values always start with 1
.
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 enum
instead of the value.
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 enum
explicitly start from 1
:
Database table
mysql> select *, `Topping` + 0 as `ToppingNumber`, `Waffle` + 0 as `WaffleNumber` from `IceCreams`;
+------------+-----------+--------------------+-----------------+---------------+--------------+
| IceCreamId | Name | Topping | Waffle | ToppingNumber | WaffleNumber |
+------------+-----------+--------------------+-----------------+---------------+--------------+
| 1 | Vanilla | None | Waffle | 1 | 1 |
| 2 | Chocolate | ChocolateSprinkles | ChocolateDipped | 2 | 3 |
+------------+-----------+--------------------+-----------------+---------------+--------------+
2 rows in set (0.00 sec)
Of course you could alter the default conversion logic in any way you want, e.g. if your database ENUM
values cannot be represented by CLR enum
names (like chocolate-dipped
), you could also (or additionally) check a custom attribute on the CLR enum
values and translate the CLR enum
value to that attribute value before sending it to the database.
from pomelo.entityframeworkcore.mysql.
@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)
);
from pomelo.entityframeworkcore.mysql.
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"),
from pomelo.entityframeworkcore.mysql.
@BartoGabriel If a property is mandatory (which an non-nullable CLR enum
would be) you would need to specify a default value for that property in the model (the default value for a non-nullable string is an empty string), e.g. .HasDefaultValue("NonCritical")
.
You could also make your property nullable, which would result in a default value of null
implicitly.
from pomelo.entityframeworkcore.mysql.
Related Issues (20)
- in 8.0.1 Is Timestamp byte[] no longer supported? HOT 11
- Implement support for Oracle MySQL's `CAST(... AT TIME ZONE ...)`
- Change return type of all `EF.Functions.DateDiff` extension methods related to units of `SECOND` or smaller from `int` to `long` HOT 4
- Just a quick question: does it support JSON data type? HOT 1
- Re-Opening connection hangs after successful query within scope of Http Request HOT 4
- Database generated value for DateTime givs an error after updating to version 8.0.1 HOT 5
- `Math.Log` number and base are in the wrong order in mysql `LOG` function HOT 1
- SEVERE ASP Api Response Time Problems with 8.0.1 and beyond HOT 10
- why string contains can be translated to like '' HOT 2
- Replace JSON_EXTRACT utilisation with JSON_VALUE HOT 5
- All primary keys altered in migration after upgrading efcore to 8.0.1 HOT 3
- Getting different SQL output for the same LINQ Query since upgrading to .NET 8 HOT 2
- ArgumentException: Option 'name' not supported. HOT 11
- Input string was not in a correct format when compare with TimeSpan HOT 1
- After upgrading from 7.0.0 to 8.0.2, SQL translation results are changed and this version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'. HOT 2
- MySQL operations break after InvalidOperationException: Can't Replace Active Reader HOT 2
- Remove explicit reference to `System.Text.Json` package from `Pomelo.EntityFrameworkCore.MySql.Json.Microsoft` package HOT 4
- After upgrading to 8.0.2 .SetAfterSaveBehavior(PropertySaveBehavior.Ignore) generates SQL that updates the column value HOT 7
- Invalid SQL Syntax when adding an entity with a default expression HOT 3
- TimeSpan.TotalHours could not be translated HOT 7
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pomelo.entityframeworkcore.mysql.