How to create a table corresponding to enum in EF6 Code First?

Question

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?

Solution 1

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
}

Solution 2

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));

Solution 3

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"

Solution 4

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.

Solution 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);
}

View more solutions

Comments

[Matt]

Possible duplicate of EF5 Code First Enums and Lookup Tables

[Matt]

nuget.org/packages/ef-enum-to-lookup Does all the hard work for you. (Shameless plug)

[Konrad Viltersten]

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?

[Alberto Monteiro]

@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!

[Konrad Viltersten]

You are very clear. And Year year = new Year{ State = States.Happy }; to you too. Yey! My first nerdy joke for this year!

[Alberto Monteiro]

@KonradViltersten eventStream.ReceiveJoke += joke => Console.WriteLine($"Pretty nice joke: {joke}, Thanks!!!");

[Ritwik Sen]

@AlbertoMonteiro : What do you call the notation @enum ?

[Alberto Monteiro]

@RitwikSen enum is a reserved word in C#, to use this word as variable/method/class name, you must use the sufix @.

[Ritwik Sen]

@AlbertoMonteiro : Thank you!

[Rahul Uttarkar]

Please update the content for Entity framework core.

[Alberto Monteiro]

@RahulUttarkar does EF do it automatically?

[Rahul Uttarkar]

@AlbertoMonteiro In Entity Framework Core doesn't support IDbSet<T> ?

[Alberto Monteiro]

It does @RahulUttarkar

[crush]

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.

[Rovann Linhalis]

Thank you very much, it helped me a lot! very cool! From Brazil =]

[AlexMelw]

@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]

/*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.

[Riz]

@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?

[IeuanW]

Is there are a net core version of this?

[the berserker]

Have you managed also to configure the foreign key constraint for ExampleClass.Faculty?

[DrEsperanto]

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...

[Aleph]

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? docs.microsoft.com/en-us/dotnet/standard/design-guidelines/…

[Alberto Monteiro]

@Aleph you can change the name of FacultyEnum to Facultaties

[Danwize]

@theberserker To get the ForeignKey constraint the ExampleClass also needs a property like this: public string FacultyName { get;set; }

[Danwize]

@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()

[DrEsperanto]

@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<TEnum> 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)

[Pavel]

@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?

[CJH]

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: stackoverflow.com/questions/53891331/…

[Konrad Viltersten]

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?

[Fraze]

@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.

[Sachin Kainth]

I like simple approaches.

[Alberto Monteiro]

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

[Konrad Viltersten]

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)?

[Somaar]

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..

[MaxouMask]

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.

[Andreea Purta]

this is def better :) Thank you!