EF5 Code First Enums and Lookup Tables

Here's a nuget package I made earlier that generates lookup tables and applies foreign keys, and keeps the lookup table rows in sync with the enum:

https://www.nuget.org/packages/ef-enum-to-lookup

Add that to your project and call the Apply method.

Documentation on github: https://github.com/timabell/ef-enum-to-lookup

It is not directly possible. EF supports enums on the same level as .NET so enum value is just named integer => enum property in class is always integer column in the database. If you want to have table as well you need to create it manually in your own database initializer together with foreign key in User and fill it with enum values.

I made some proposal on user voice to allow more complex mappings. If you find it useful you can vote for the proposal.

I wrote a little helper class, that creates a database table for the enums specified in the UserEntities class. It also creates a foreign key on the tables that referencing the enum.

So here it is:

public class EntityHelper
{

    public static void Seed(DbContext context)
    {
        var contextProperties = context.GetType().GetProperties();

        List<PropertyInfo> enumSets =  contextProperties.Where(p  =>IsSubclassOfRawGeneric(typeof(EnumSet<>),p.PropertyType)).ToList();

        foreach (var enumType in enumSets)
        {
            var referencingTpyes = GetReferencingTypes(enumType, contextProperties);
            CreateEnumTable(enumType, referencingTpyes, context);
        }
    }

    private static void CreateEnumTable(PropertyInfo enumProperty, List<PropertyInfo> referencingTypes, DbContext context)
    {
        var enumType = enumProperty.PropertyType.GetGenericArguments()[0];

        //create table
        var command = string.Format(
            "CREATE TABLE {0} ([Id] [int] NOT NULL,[Value] [varchar](50) NOT NULL,CONSTRAINT pk_{0}_Id PRIMARY KEY (Id));", enumType.Name);
        context.Database.ExecuteSqlCommand(command);

        //insert value
        foreach (var enumvalue in Enum.GetValues(enumType))
        {
            command = string.Format("INSERT INTO {0} VALUES({1},'{2}');", enumType.Name, (int)enumvalue,
                                    enumvalue);
            context.Database.ExecuteSqlCommand(command);
        }

        //foreign keys
        foreach (var referencingType in referencingTypes)
        {
            var tableType = referencingType.PropertyType.GetGenericArguments()[0];
            foreach (var propertyInfo in tableType.GetProperties())
            {
                if (propertyInfo.PropertyType == enumType)
                {
                    var command2 = string.Format("ALTER TABLE {0} WITH CHECK ADD  CONSTRAINT [FK_{0}_{1}] FOREIGN KEY({2}) REFERENCES {1}([Id])",
                        tableType.Name, enumProperty.Name, propertyInfo.Name
                        );
                    context.Database.ExecuteSqlCommand(command2);
                }
            }
        }
    }

    private static List<PropertyInfo> GetReferencingTypes(PropertyInfo enumProperty, IEnumerable<PropertyInfo> contextProperties)
    {
        var result = new List<PropertyInfo>();
        var enumType = enumProperty.PropertyType.GetGenericArguments()[0];
        foreach (var contextProperty in contextProperties)
        {

            if (IsSubclassOfRawGeneric(typeof(DbSet<>), contextProperty.PropertyType))
            {
                var tableType = contextProperty.PropertyType.GetGenericArguments()[0];

                foreach (var propertyInfo in tableType.GetProperties())
                {
                    if (propertyInfo.PropertyType == enumType)
                        result.Add(contextProperty);
                }
            }
        }

        return result;
    }

    private static bool IsSubclassOfRawGeneric(Type generic, Type toCheck)
    {
        while (toCheck != null && toCheck != typeof(object))
        {
            var cur = toCheck.IsGenericType ? toCheck.GetGenericTypeDefinition() : toCheck;
            if (generic == cur)
            {
                return true;
            }
            toCheck = toCheck.BaseType;
        }
        return false;
    }

    public class EnumSet<T>
    {
    }
}

using the code:

public partial class UserEntities : DbContext{
    public DbSet<User> User { get; set; }
    public EntityHelper.EnumSet<UserType> UserType { get; set; }

    public static void CreateDatabase(){
        using (var db = new UserEntities()){
            db.Database.CreateIfNotExists();
            db.Database.Initialize(true);
            EntityHelper.Seed(db);
        }
    }

}

I have created a package for it

https://www.nuget.org/packages/SSW.Data.EF.Enums/1.0.0

Use

EnumTableGenerator.Run("your object context", "assembly that contains enums");

"your object context" - is your EntityFramework DbContext "assembly that contains enums" - an assembly that contains your enums

Call EnumTableGenerator.Run as part of your seed function. This will create tables in sql server for each Enum and populate it with correct data.