74

I've followed MSDN on how to handle enumerations in Code First for EF6. It worked, as supposed to but the field in the created table that refers to the enumerator is a simple int.

I'd prefer a second table to be produced, the values of which would follow the definition of the enumerator in C# code. So, instead of only getting a table corresponding to Department in the example on MSDN, I'd also like to see a second table populated by the items from Faculty.

public enum Faculty { Eng, Math, Eco }     

public partial class Department 
{ 
  [Key] public Guid ID { get; set; } 
  [Required] public Faculty Name { get; set; } 
}

Researching the issue, I stumbled upon a solution, which suggests creating a table for the enumeration and populating it explicitly by seeding.

It appear to me as a cumbersome approach and a lot of work that should be handled automagically. After all, the system knows what actual values that constitute the enumeration. From DB point of view it's still data rows, just as the entities that I create but from OO aspect, it's not really a data - rather a type (loosely expressed) that can assume a finite and onbeforehand known number of states.

Is the approach of populating the table "manually" recommended?

Walt Ritscher
  • 6,977
  • 1
  • 28
  • 35
Konrad Viltersten
  • 36,151
  • 76
  • 250
  • 438
  • 1
    Possible duplicate of [EF5 Code First Enums and Lookup Tables](https://stackoverflow.com/questions/11167665/ef5-code-first-enums-and-lookup-tables) – Tim Abell May 16 '18 at 15:12
  • 1
    https://www.nuget.org/packages/ef-enum-to-lookup Does all the hard work for you. (Shameless plug) – Tim Abell May 16 '18 at 15:14

8 Answers8

132

Since EF doesn't handle it automatically, yes, this is the recommend way.

I suggest some modifications in article that you provided.

Rename your enum

public enum FacultyEnum { Eng, Math, Eco }

Create a class that represent the table

public class Faculty
{
    private Faculty(FacultyEnum @enum)
    {
        Id = (int)@enum;
        Name = @enum.ToString();
        Description = @enum.GetEnumDescription();
    }

    protected Faculty() { } //For EF

    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    [Required, MaxLength(100)]
    public string Name { get; set; }

    [MaxLength(100)]
    public string Description { get; set; }

    public static implicit operator Faculty(FacultyEnum @enum) => new Faculty(@enum);

    public static implicit operator FacultyEnum(Faculty faculty) => (FacultyEnum)faculty.Id;
}

Your model reference the class

public class ExampleClass
{
    public virtual Faculty Faculty { get; set; }
}

Create a extension method to get description from enum and seed values

using System;
using System.ComponentModel;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;

public static class Extensions
{
    public static string GetEnumDescription<TEnum>(this TEnum item)
        => item.GetType()
               .GetField(item.ToString())
               .GetCustomAttributes(typeof(DescriptionAttribute), false)
               .Cast<DescriptionAttribute>()
               .FirstOrDefault()?.Description ?? string.Empty;

    public static void SeedEnumValues<T, TEnum>(this IDbSet<T> dbSet, Func<TEnum, T> converter)
        where T : class => Enum.GetValues(typeof(TEnum))
                               .Cast<object>()
                               .Select(value => converter((TEnum)value))
                               .ToList()
                               .ForEach(instance => dbSet.AddOrUpdate(instance));
}

Add the seed in Configuration.cs

protected override void Seed(Temp.MyClass context)
{
    context.Facultys.SeedEnumValues<Faculty, FacultyEnum>(@enum => @enum);
    context.SaveChanges();
}

Add the enum table in your DbContext

public class MyClass : DbContext
{
    public DbSet<ExampleClass> Examples { get; set; }
    public DbSet<Faculty> Facultys { get; set; }
}

Use it

var example = new ExampleClass();
example.Faculty = FacultyEnum.Eng;

if (example.Faculty == FacultyEnum.Math)
{
    //code
}

To remember

If you don't add virtual in Faculty property, you must use Include method from DbSet to do Eager Load

var exampleFromDb = dbContext.Examples.Include(x => x.Faculty).SingleOrDefault(e => e.Id == 1);
if (example.Faculty == FacultyEnum.Math)
{
    //code
}

If Faculty property is virtual, then just use it

var exampleFromDb = dbContext.Examples.Find(1);
if (example.Faculty == FacultyEnum.Math)
{
    //code
}
Alberto Monteiro
  • 5,989
  • 2
  • 28
  • 40
  • 4
    Very well worded answer. +1 for the effort alone. Now, as I understand that EF doesn't handle the enumerations automatically by its design, I wonder if it's just a choice made by the designers or if there's a logical, technical or, perhaps, semantic reason behind it. **If** it would handle it the way we lazies prefer, would that lead to conceptual paradoxes along the way? I see that it can be resolved by a table with value as integer and two string - the name and the description. Am I oversimplifying the issue? – Konrad Viltersten Jan 01 '16 at 17:37
  • 2
    @KonradViltersten I think that everything depends. So EF try to fit the most general need of most people. I never had to create a table for a Enum, just using "int" fit my need. IMHO I think that EF design choose that, because most people use in this way and to have better performance and DB Size optimization. Also I don't think so that this is going to lead conceptual paradoxes, if there is a valid justification to create another table, got for it. Am I clear? Btw Happy new Year! – Alberto Monteiro Jan 01 '16 at 17:49
  • 7
    You are very clear. And *Year year = new Year{ State = States.Happy };* to you too. Yey! My first nerdy joke for this year! – Konrad Viltersten Jan 01 '16 at 17:53
  • 6
    @KonradViltersten `eventStream.ReceiveJoke += joke => Console.WriteLine($"Pretty nice joke: {joke}, Thanks!!!");` – Alberto Monteiro Jan 01 '16 at 17:55
  • @AlbertoMonteiro : What do you call the notation `@enum` ? – Ritwik Sen Oct 20 '16 at 07:16
  • 5
    @RitwikSen enum is a reserved word in C#, to use this word as variable/method/class name, you must use the sufix @. – Alberto Monteiro Oct 22 '16 at 07:09
  • @AlbertoMonteiro : Thank you! – Ritwik Sen Oct 22 '16 at 07:12
  • Please update the content for Entity framework core. – Rahul Uttarkar Sep 25 '17 at 16:38
  • @RahulUttarkar does EF do it automatically? – Alberto Monteiro Sep 25 '17 at 17:21
  • @AlbertoMonteiro In Entity Framework Core doesn't support IDbSet ? – Rahul Uttarkar Sep 26 '17 at 19:08
  • It does @RahulUttarkar – Alberto Monteiro Sep 28 '17 at 00:58
  • @AlbertoMonteiro I think that `public static implicit operator Faculty(FacultyEnum @enum) => new Faculty(@enum);` has to be changed to `explicit` instead of `implicit`. Because on `if (example.Faculty == FacultyEnum.Math){...}` the `FacultyEnum.Math` is converted to `Faculty` object (***ValueType*** and ***ReferenceType*** cannot be compared *directly*, – AlexMelw May 28 '18 at 08:35
  • **/*CONTINUATION*/** so the ***ValueType*** will be implicitly converted to ***ReferenceType***, because of the overloaded `implicit operator`). Hence `ob1 == obj2` is **false** (except the case when `==` operator is overloaded as well). That's why I propose `explicit operator` overloading for `FacultyEnum to Faculty` and `implicit operator` overloading for `Faculty to FacultyEnum` which will produce Faculty implicit conversion to FacultyEnum, the result of which will be `val1 == val2` is **true**. – AlexMelw May 28 '18 at 08:47
  • @AlbertoMonteiro, the seed method AddOrUpdate generates a constraint violation for me when it's run against the db more than once, as in my case, the "Name" is supposed to be unique. Any way around it? – Riz May 31 '18 at 06:54
  • Is there are a net core version of this? – IeuanW Jul 23 '18 at 17:17
  • This solution seems to break the naming conventions for Enums as it adds the suffix Enum to the name... Is there an alternative suggestion for how to name the enums sensibly? https://learn.microsoft.com/en-us/dotnet/standard/design-guidelines/names-of-classes-structs-and-interfaces#naming-enumerations – Aleph Nov 07 '18 at 15:49
  • @Aleph you can change the name of FacultyEnum to Facultaties – Alberto Monteiro Nov 08 '18 at 18:39
  • @AlbertoMonteiro it seems like AddOrUpdate has been depricated in .NET Core, and no good seed alternative existed until Core 2.1. Is there an .NET Core 2.1+ version of this? – Pavel Dec 04 '18 at 18:23
  • Love this answer! However when I tried adding other items into my context that uses this enumerator, it started trying to add entries into the new Enum-based table every time the enumerator was used in another table... I have posted the following: https://stackoverflow.com/questions/53891331/seeding-from-enum-duplicate-primary-key-error?noredirect=1#comment94629892_53891331 – CJH Dec 22 '18 at 22:48
11

Based on @Alberto Monteiro answer i've created generic class in case when you have several tables. The notice here is that Id is the type of TEnum. Using it in such way will provide option to use Enum for declaring property type.

public class Question
{
    public QuestionTypeEnum QuestionTypeId { get; set; } // field property

    public QuestionType QuestionType { get; set; } // navigation property
}

By default Enum using integers, so the db provider will create field with "int" type.

EnumTable.cs

    public class EnumTable<TEnum>
        where TEnum : struct
    {
        public TEnum Id { get; set; }
        public string Name { get; set; }

        protected EnumTable() { }

        public EnumTable(TEnum enumType)
        {
            ExceptionHelpers.ThrowIfNotEnum<TEnum>();

            Id = enumType;
            Name = enumType.ToString();
        }

        public static implicit operator EnumTable<TEnum>(TEnum enumType) => new EnumTable<TEnum>(enumType);
        public static implicit operator TEnum(EnumTable<TEnum> status) => status.Id;
    }

ExceptionHelpers.cs

static class ExceptionHelpers
{
    public static void ThrowIfNotEnum<TEnum>()
        where TEnum : struct
    {
        if (!typeof(TEnum).IsEnum)
        {
            throw new Exception($"Invalid generic method argument of type {typeof(TEnum)}");
        }
    }
}

Now you just can inherit the EnumTable

public enum QuestionTypeEnum
{
    Closed = 0,
    Open = 1
}

public class QuestionType : EnumTable<QuestionTypeEnum>
{
    public QuestionType(QuestionTypeEnum enumType) : base(enumType)
    {
    }

    public QuestionType() : base() { } // should excplicitly define for EF!
}

Seed the values

context.QuestionTypes.SeedEnumValues<QuestionType, QuestionTypeEnum>(e => new QuestionType(e));
unsafePtr
  • 1,591
  • 2
  • 17
  • 27
  • 3
    This of course breaks the implict operators. To combat this, I made `EnumTable` abstract, and removed the implicit operators from it. I then added them to the derived class. If a developer forgets to add the implicit operators, then they will get a compiler error about missing implicit casts when they try to assign to a reference of the type or when they try to setup the seeding in configuration. I found this acceptable. – crush Oct 24 '17 at 04:40
  • Thank you very much, it helped me a lot! very cool! From Brazil =] – Rovann Linhalis Oct 30 '17 at 21:30
6

Another approach that works (and feels simpler to me) in EF Core:

Your Enum

public enum Color
{
    Red = 1,
    Blue = 2,
    Green = 3,
}

Db Tables

public class CustomObjectDto
{
    public int ID { get; set; }

    // ... other props

    public Color ColorID { get; set; }
    public ColorDto ColorDto { get; set; }
}

public class ColorDto
{
    public Color ID { get; set; }
    public string Name { get; set; }
}

Your DbContext

public class Db : DbContext
{
    public Db(DbContextOptions<Db> options) : base(options) { }

    public DbSet<CustomObjectDto> CustomObjects { get; set; }
    public DbSet<ColorDto> Colors { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Seed database with all Colors
        foreach (Color color in Enum.GetValues(typeof(Color)).Cast<Color>())
        {
            ColorDto colorDto = new ColorDto
            {
                ID = color,
                Name = color.ToString(),
            };

            modelBuilder.Entity<ColorDto>().HasData(colorDto);
        }
    }
}

In code I basically only use the enum Color (never ColorDto). But it's still nice to have the 'Colors' table with an FK in the 'CustomObjects' table for sql queries and views.

Community
  • 1
  • 1
  • Yeah, its simpler, but you lost some nice features, like comparing the enum with the class, useful in if statements and also your approach does not allow "automatic" name with spaces, the are limited to enum string name – Alberto Monteiro Apr 29 '20 at 12:10
  • this is def better :) Thank you! – Andreea Purta Oct 28 '21 at 09:49
5

Another possibility, if you want to keep your model simpler, POCO style, use the enum as a property that will be stored as an integer by entity framework.

Then, if you want the "enum tables" to be created and updated in your DB, I recommend using the nuget package https://github.com/timabell/ef-enum-to-lookup and use it in a EF Migration seed method for example:

public enum Shape
{
    Square,
    Round
}

public class Foo
{
    public int Id { get; set; }
    public Shape Shape { get; set; }
}

public class MyDbContext : DbContext
{
    public DbSet<Foo> Foos { get; set; }
}

using(var context = new MyDbContext())
{
    var enumToLookup = new EnumToLookup
    {
        TableNamePrefix = string.Empty,
        NameFieldLength = 50,
        UseTransaction = true
    };
    enumToLookup.Apply(context);
}

This will create the "Shape" table with 2 rows named Square and Round, with the relevant foreign key constraint in the table "Foo"

Jason Foglia
  • 2,414
  • 3
  • 27
  • 48
Michael
  • 791
  • 9
  • 9
5

Excellent @AlbertoMonterio! To get this to work with ASP.NET CORE / EF Core I made a few adjustments to Alberto's solution.

For brevity, only the modifications are shown below:

Create a extension method to get description from enum and seed values

using System;
using System.ComponentModel;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;
using Microsoft.EntityFrameworkCore; //added
using Microsoft.EntityFrameworkCore.Metadata.Builders; //added

public static class Extensions
{
    //unchanged from alberto answer
    public static string GetEnumDescription<TEnum>(this TEnum item)
        => item.GetType()
               .GetField(item.ToString())
               .GetCustomAttributes(typeof(DescriptionAttribute), false)
               .Cast<DescriptionAttribute>()
               .FirstOrDefault()?.Description ?? string.Empty;

    //changed
    public static void SeedEnumValues<T, TEnum>(this ModelBuilder mb, Func<TEnum, T> converter)
    where T : class => Enum.GetValues(typeof(TEnum))
                           .Cast<object>()
                           .Select(value => converter((TEnum)value))
                           .ToList()
                            .ForEach(instance => mb.Entity<T>().HasData(instance));
}

Add the seed in Configuration.cs

Add Seeding to OnModelCreating of DataContext

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.SeedEnumValues<Faculty, EnumEntityRole>(e => e);
}
Fraze
  • 908
  • 2
  • 8
  • 20
  • Wouldn't it make the seed only happen when the model is changed? I find it more likely to be of gain to have the seed repopulate the DB **each time the app starts**. Or am I missing something? – Konrad Viltersten May 23 '19 at 16:52
  • @KonradViltersten it would seed when the model is initialized. If you want to seed on startup you could just add `context.Database.Migrate();` in Startup's `Configure()` method. – Fraze May 23 '19 at 17:16
4

I might be a bit late for the party but I didn't find the answer I was looking for here.

While looking around in the EntityFramework documentation I found the solution, it is the first example in Value Conversions

With this you can make a nice extension method if you want. i.e.

public static void HasEnum<TEntity, TProperty>(this EntityTypeBuilder<TEntity> entityBuilder, Expression<Func<TEntity, TProperty>> propertyExpression)
        where TEntity : class
        where TProperty : Enum
    {
        entityBuilder.Property(propertyExpression)
            .HasConversion(
                v => v.ToString(),
                v => (TProperty)Enum.Parse(typeof(TProperty), v)
            );
    }

Then use it in your OnModelCreating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<YourEntity>()
        .HasEnum(e => e.YourProperty);
}
Somaar
  • 135
  • 2
  • 8
  • I'm the original poster of this. Not sure if it's the case, but I recall that when I asked, 4 years and 5 moths ago, there was noting like *HasEnum*. If there was, I regret missing that feature. If there wasn't, I'm glad it's been added. +1 for the attempt and, also, can you elaborate as to what's being created in the tables precisely (i.e. what DB schema will be a result if this approach)? – Konrad Viltersten May 09 '20 at 16:00
  • Thanks for getting back at it! The *HasEnum* method is an extension which I created myself. This way you can use it with the modelbuilder in a clean way which is readable for others. In the database it will be entered as an enum, which is in essence a string with a list of allowed values. So the value of the enum will be converted to string when _saved_ in the database. On retrieval the value is parsed to your enum type. I like to use enums because it clearly shows what the value is opposed to an int i.e. and makes it less likely to make mistakes. Hope this clears it up a bit.. – Somaar May 10 '20 at 12:49
  • I might need to add that I think that this solution is only valid with EF Core, and when this question was asked (4 years ago) I'm not sure it was actually existing. – MaxouMask Nov 12 '20 at 16:37
  • For this is the most elegant way of solving this problem. +1 from me – Benj Dec 07 '22 at 10:15
2

You should add : byte in front of enum declaration :

enum MyFieldEnum : byte{
    one = 1,
    two = 2,
    three = 4
}

In database, you should see TINYINT and no need to casting !

Atzi
  • 457
  • 1
  • 6
  • 16
0

UPDATE: I found a better way that works well in EntityFrameworkCore 5.0.8

Add JsonConverter attributes to your enum

[Newtonsoft.Json.JsonConverter(typeof(StringEnumConverter))]
public enum FacultyEnum
{
    [EnumMember(Value = "English Professor")]
    Eng,
    [EnumMember(Value = "Math Professor")]
    Math,
    [EnumMember(Value = "Economics Professor")]
    Eco
}

Create a class the represents the table

public class Faculty
{
    public int Id { get; set; }

    public string Name { get; set; }

    public FacultyEnum Description { get; set; }
}

Use Fluent API in OnModelCreating in your DbContext to use the enum strings and set check constraints

        var enumToString = new EnumToStringConverter<FacultyEnum>();
        modelBuilder.Entity<Faculty>(entity =>
        {
            entity.ToTable(nameof(FacultyMembers));
            //convert enums to string
            entity.Property(e => e.Description).HasConversion(enumToString); 
            //build check constraint from enum
            var allowedEnumStrings = string.Join(',',
                typeof(Faculty).GetMembers()
                    .Select(x => x.GetCustomAttribute(typeof(EnumMemberAttribute), false)).Where(x => x != null)
                    .Select(x => $"'{((EnumMemberAttribute)x).Value}'"));
            entity.HasCheckConstraint($"CK_{nameof(FacultyMembers)}_{nameof(Faculty.Description)}", $"{nameof(Faculty.Description)} in ({allowedEnumStrings})");
        });

Old Way

Alberto Monteiro answered this very well. I had to make a few adjustments to get it to work with EF core.

Rename your enum and add description decorators

public enum FacultyEnum 
{
    [Description("English Professor")]
    Eng, 
    [Description("Math Professor")]
    Math, 
    [Description("Economics Professor")]
    Eco 
}

Create a class that represent the table

public class Faculty
{
    private Faculty(FacultyEnum @enum)
    {
        Id = (int)@enum;
        Name = @enum.ToString();
        Description = @enum.GetEnumDescription();
    }

    protected Faculty() { } //For EF

    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    [Required, MaxLength(100)]
    public string Name { get; set; }

    [MaxLength(100)]
    public string Description { get; set; }

    public static implicit operator Faculty(FacultyEnum @enum) => new Faculty(@enum);

    public static implicit operator FacultyEnum(Faculty faculty) => (FacultyEnum)faculty.Id;
}

Your model reference the class

public class ExampleClass
{
    public virtual Faculty Faculty { get; set; }
}

Create a extension method to get description from enum and seed values

using System;
using System.ComponentModel;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;

public static class Extensions
{
    public static string GetEnumDescription<TEnum>(this TEnum item)
        => item.GetType()
               .GetField(item.ToString())
               .GetCustomAttributes(typeof(DescriptionAttribute), false)
               .Cast<DescriptionAttribute>()
               .FirstOrDefault()?.Description ?? string.Empty;
}

Add the seed in YourDbContext.cs

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Faculty>().HasData(FacultyEnum.Eng, FacultyEnum.Math, FacultyEnum.Eco);
    }

Add the enum table in your DbContext

public class MyClass : DbContext
{
    public DbSet<ExampleClass> Examples { get; set; }
    public DbSet<Faculty> Facultys { get; set; }
}

Use it

var example = new ExampleClass();
example.Faculty = FacultyEnum.Eng;

if (example.Faculty == FacultyEnum.Math)
{
    //code
}

To remember

If you don't add virtual in Faculty property, you must use Include method from DbSet to do Eager Load

var exampleFromDb = dbContext.Examples.Include(x => x.Faculty).SingleOrDefault(e => e.Id == 1);
if (example.Faculty == FacultyEnum.Math)
{
    //code
}

If Faculty property is virtual, then just use it

var exampleFromDb = dbContext.Examples.Find(1);
if (example.Faculty == FacultyEnum.Math)
{
    //code
}
Danwize
  • 141
  • 11
  • Have you managed also to configure the foreign key constraint for `ExampleClass.Faculty`? – the berserker Sep 12 '18 at 11:38
  • 1
    I'm struggling to justify this approach for my own code, and I'm not sure how your example is even working. Your table is seeded with an entry for `FacultyEnum.Eng`, so when you set `example.Faculty = FacultyEnum.Eng` you should be creating a duplicate entry. In my case I get a UNIQUE constraint failure, and have to use something like `example.Faculty = dbContext.Facultys.Single(t => t.Id == FacultyEnum.Eng)` to get the actual table entry for that enum. The implicit operator is clearly creating a `new` instance, although it is not obvious due to the implicitness... – DrEsperanto Nov 05 '18 at 20:13
  • @theberserker To get the ForeignKey constraint the ExampleClass also needs a property like this: `public string FacultyName { get;set; }` – Danwize Nov 09 '18 at 21:49
  • @DrEsperanto I noticed the same problem. I wish it did work implicitly. I had to do something like this after adding a new FacultyName property to my example class: `dbContext.Examples.First().FacultyName = TruckStatusEnum.Closed.Tostring()` – Danwize Nov 09 '18 at 21:55
  • @Danwize Once getting a better understanding of the framework I decided against this method with implicit operators. It only really makes it easy (but complex) to seed the tables in the database, and I only do that once. I seeded them instead by just converting the enums into a list of my EnumTable objects (I used generic EnumTable objects with Id and Name parameters, and have a static method on my context to convert an enum to a list of EnumTables. I then use LINQ queries on my model to return the entry with an Id of a given enum value (so I can still get help from intellisense) – DrEsperanto Nov 10 '18 at 04:01