Code First Enumerations put into Lookup Tables

Question

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

Answer 1

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]
    [Area("Admin")]
    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));";
                _db.Database.ExecuteSqlCommand(tableSql);

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

Answer 2

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
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    [Required]
    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
///     SHOULD NOT BE AVAILABLE IN ENTITY MODELS
/// </summary>
[Table("AddressTypes", Schema = "Lookup")]
public class AddressType : LookupTableBase {}

Here is the Enum that goes with this Lookup Table:

public enum AddressTypes
{
    [StringValue("")]
    Unknown = 0,

    [StringValue("Home")]
    Home = 1,

    [StringValue("Mailing")]
    Mailing = 2,

    [StringValue("Business")]
    Business = 3
}

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

AddressTypes.Home.GetStringValue();

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:

modelBuilder.Entity<AddressType>()
            .Property(x => x.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

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());
context.SaveChanges();

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.