Code First Enumerations put into Lookup Tables

c# entity-framework enums


I have worked in a lot of shops where they ran a Database First Model so Lookup Tables were always required. Your lookup table had to match your Enums so that you kept database integrity. I 100% agree with this idea, but have found that when it comes to the Code First Model, this is not available out of the box. I did read somewhere that the EF Team may be adding the ability to dynamically have Enums added to your DB (via migrations) in EF7 but they warned that it's not a promise.

So how do you (if at all) accomplish this? I am going to provide my solution below in an answer and look forward to your feedback.

I am using EF 6.1.3 and .NET 4.5.1

7/12/2015 10:35:51 PM

Accepted Answer

So I am not going to lie, my solution is a bit in-depth but I have been using it now for the past few days and I find it works exactly as I need it to.

Let's start at the top, my base class I created:

public abstract class LookupTableBase
    public int Id { get; set; }

    public string Name { get; set; }

Here is an example of one of my lookup table Entity Models:

/// <summary>
///     Lookup Table for Enumeration AddressTypes
///     File Reference: DataAccessLayer/Enumerations/Locators.cs
///     DO NOT USE
/// </summary>
[Table("AddressTypes", Schema = "Lookup")]
public class AddressType : LookupTableBase {}

Here is the Enum that goes with this Lookup Table:

public enum AddressTypes
    Unknown = 0,

    Home = 1,

    Mailing = 2,

    Business = 3

The StringValue Attribute is a custom attribute I created (based on examples I found online) that allow me to call:


Which will return the string value: Home.

I add the Lookup Entity Model to my DbSets so the table will be created but I never directly reference the Lookup Entity Models in any of my other Entity Models. Its sole purpose is to create lookup tables in the DB so that I can create Foreign Key Constraints against them.

public DbSet<AddressType> AddressTypes { get; set; }

In my OnModelCreating Method for my Context, I did have to add this because the Data Annotation did not seem to hold all the way through:

            .Property(x => x.Id)

In my Migration's Configuration file, I add this into the Seed Method:

var addressTypeCount = Enum.GetValues(typeof (AddressTypes)).Length;
var addressTypes = new List<AddressType>();
for (var i = 1; i < addressTypeCount; i++) {
    addressTypes.Add(new AddressType {
                                         Id = i,
                                         Name = ((AddressTypes)i).GetStringValue()
context.AddressTypes.AddOrUpdate(c => c.Id, addressTypes.ToArray());

Last, in the Migration file itself I move all the lookup table creation methods to the top of the list, now I can add Foreign Key Constraints to any table that references that enum. In my case, I took it one step further. Since the Migration Class is a partial, I created another partial class to match it. Created two methods:

public void LookupDataUp()
public void LookupDataDown()

In the LookupDataUp method, I add all my custom Foreign Keys and Indexes and in the LookupDataDown I Remove all my custom Foreign Keys and Indexes.

When I run Update-Database, all my tables that used to have some integer value that represented something (in this case an AddressType) but had no real value, now have a value that can be seen by linking it to its lookup table.

I will admit, this seems like a lot of work just to get some small amount of data into the database but now every time I remove/change/add new items to my enum, it's automatically pushed to the DB. Plus as I stated in the above question, this creates database integrity by having the foreign key constraint on the 'integer' field.

2/1/2017 5:09:23 PM

Popular Answer

If you aren't looking to muddy up your context but still want your enums in the database for troubleshooting & manual queries. It's also not ideal, but you can do an at-once migration when you need it. Obviously there's some cleanup and tweaking to be done which may vary depending on your use case.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using LookupExample.Data;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace LookupExample.Areas.Admin.Controllers
    // [Authorize]
    public class SetupController : Controller
        private ApplicationDbContext _db;

        public SetupController(ApplicationDbContext dbContext)
            _db = dbContext;

        public IActionResult Enums()
            var enums = Assembly.GetExecutingAssembly().GetTypes()
                .Where(ttype => ttype.IsEnum && ttype.IsPublic && ttype.Namespace.StartsWith("LookupExample.Models"));

            var dictionary = enums.ToDictionary(EnumTableName, EnumDictionary);

            if (dictionary.Count <= 0) return Json(dictionary);

#pragma warning disable EF1000 // Possible SQL injection vulnerability.
            foreach (var kvp in dictionary)
                var table = kvp.Key;
                var tableSql = $"IF OBJECT_ID('{table}', 'U') IS NOT NULL DROP TABLE {table}; CREATE TABLE {table} ( Id int, Val varchar(255));";

                if (kvp.Value.Count <= 0) continue;

                var insertSql = $"INSERT INTO {table} (Id, Val) VALUES ( @P0, @P1);";
                foreach (var row in kvp.Value)
                    _db.Database.ExecuteSqlCommand(insertSql, row.Key, row.Value);
#pragma warning restore EF1000 // Possible SQL injection vulnerability.

            return Json(dictionary);

        private string EnumTableName(Type eenum)
            var namespaceModifier = Regex.Replace(Regex.Replace(eenum.Namespace, @"^LookupExample\.Models\.?", ""), @"\.?Enums$", "").Replace(".", "_");
            if (namespaceModifier.Length > 0)
                namespaceModifier = namespaceModifier + "_";
            return "dbo.Enum_" + namespaceModifier + eenum.Name; // TODO enum schema?

        private Dictionary<int, string> EnumDictionary(Type eenum)
            return Enum.GetValues(eenum).Cast<int>().ToDictionary(e => e, e => Enum.GetName(eenum, e));

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow