EF5 Code First Enums and Lookup Tables

Question

I'd like to define an enum for EF5 to use, and a corresponding lookup table. I know EF5 now supports enums, but out-of-the-box, it seems it only supports this at the object level, and does not by default add a table for these lookup values.

For example, I have a User entity:

public class User
{
    int Id { get; set; }
    string Name { get; set; }
    UserType UserType { get; set; }
}

And a UserType enum:

public enum UserType
{
    Member = 1,
    Moderator = 2,
    Administrator = 3
}

I would like for database generation to create a table, something like:

create table UserType
(
    Id int,
    Name nvarchar(max)
)

Is this possible?

Answer 1

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.

Answer 2

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.

Answer 3

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

}

Answer 4

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

Answer 5

you must customize your workflow of generation

1. Copy your default template of generation TablePerTypeStrategy

Location : \Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen.

2. Add custom activity who realize your need (Workflow Foundation)

3. Modify your section Database Generation Workflow in your project EF

Answer 6

I have included this answer as I've made some additional changes from @HerrKater

I made a small addition to Herr Kater's Answer (also based on Tim Abell's comment). The update is to use a method to get the enum value from the DisplayName Attribute if exists else split the PascalCase enum value.

 private static string GetDisplayValue(object value)
 {
   var fieldInfo = value.GetType().GetField(value.ToString());

   var descriptionAttributes = fieldInfo.GetCustomAttributes(
     typeof(DisplayAttribute), false) as DisplayAttribute[];

   if (descriptionAttributes == null) return string.Empty;
   return (descriptionAttributes.Length > 0)
   ? descriptionAttributes[0].Name
   : System.Text.RegularExpressions.Regex.Replace(value.ToString(), "([a-z](?=[A-Z])|[A-Z](?=[A-Z][a-z]))", "$1 ");
 }

Update Herr Katers example to call the method:

 command = string.Format("INSERT INTO {0} VALUES({1},'{2}');", enumType.Name, (int)enumvalue,
                                        GetDisplayValue(enumvalue));

Enum Example

public enum PaymentMethod
{
    [Display(Name = "Credit Card")]
    CreditCard = 1,

    [Display(Name = "Direct Debit")]
    DirectDebit = 2
}