Efcore: Enum as Lookup Table

[gojanpaolo]

Is it possible to create a a Lookup Table from an Enum and apply foreign key constraints on related entities.

In EF6, we can use this library:

• https://github.com/timabell/ef-enum-to-lookup
  
  
  
  • Creates lookup tables and foreign key constraints based on the enums used in your model.
  
  

This may also be related to #12206.

Would this require EF to treat Enum as an entity?

References:

• https://stackoverflow.com/questions/34557574/how-to-create-a-table-corresponding-to-enum-in-ef6-code-first
• https://stackoverflow.com/questions/11167665/ef5-code-first-enums-and-lookup-tables
• https://stackoverflow.com/questions/31372549/code-first-enumerations-put-into-lookup-tables
• http://gentlelogic.blogspot.com/2014/07/ef-code-first-create-table-out-of-enum.html

[ajcvickers]

@gojanpaolo Thanks for suggesting this. We discussed this in triage and it is not something we are planning to implement. We might consider a community PR to add this if a reasonable design is proposed and the implementation is not too complex.

[Kukkimonsuta]

This seem to be possible already with data seeding (see below).

While playing with this concept I've found some limitations though:

• when data seeding has an Enum as key it doesn't seem to be able to do compare and always recreates deletes and recreates all data (even when creating migration that actually has no changes.. possibly bug? https://github.com/aspnet/EntityFrameworkCore/issues/12194), so I've worked around it by introducing a additional numeric key
• when you change enum value it will get recreated by delete/create (which is expected), but you might be surprised that all data with that value has cascade deleted with it because cascade is the EF default, so maybe use DeleteBehavior.Restrict or don't use FK here at all.

    /// <summary>
    /// Generic entity representing a enum.
    /// </summary>
    /// <typeparam name="T">Enum type.</typeparam>
    public class EnumLookup<T>
        where T : Enum
    {
        public EnumLookup()
        {
        }
        public EnumLookup(T value)
        {
            Id = Convert.ToInt32(value);
            Value = value;
            Name = value.ToString();
        }

        public int Id { get; set; }
        public T Value { get; set; }
        public string Name { get; set; }
    }

    public static class EnumLookupExtensions
    {
        /// <summary>
        /// Scan all registered entities and build a enum lookup table for any enum properties.
        /// </summary>
        /// <param name="modelBuilder">The model builder.</param>
        /// <param name="createForeignKeys">Create foreign keys. Note that default delete behavior of EF is Cascade, so changes in enums might delete your data!</param>
        public static void CreateEnumLookupTable(this ModelBuilder modelBuilder, bool createForeignKeys = false)
        {
            foreach (var property in modelBuilder.Model.GetEntityTypes().SelectMany(t => t.GetProperties()).ToArray())
            {
                var entityType = property.DeclaringEntityType;
                var propertyType = property.ClrType;

                if (!propertyType.IsEnum)
                    continue;

                var concreteType = typeof(EnumLookup<>).MakeGenericType(propertyType);
                var enumLookupBuilder = modelBuilder.Entity(concreteType);
                enumLookupBuilder.HasAlternateKey(nameof(EnumLookup<Enum>.Value));

                var data = Enum.GetValues(propertyType).Cast<object>()
                    .Select(v => Activator.CreateInstance(concreteType, new object[] { v }))
                    .ToArray();

                enumLookupBuilder.HasData(data);

                if (createForeignKeys)
                {
                    modelBuilder.Entity(entityType.Name)
                        .HasOne(concreteType)
                        .WithMany()
                        .HasPrincipalKey(nameof(EnumLookup<Enum>.Value))
                        .HasForeignKey(property.Name);
                }
            }
        }
    }

Sample usage:

    public enum AssignmentType
    {
        Bug = 100,
        Feature = 200,
        Enhancement = 300,
    }

    public enum AssignmentPriority
    {
        Urgent = 100,
        Urgenter = 200,
        Urgentest = 300,
        Critical = 1105,
        Criticaler = 1200,
        Criticalest = 9001
    }

    public class Assignment
    {
        public int Id { get; set; }

        public AssignmentType Type { get; set; }

        public AssignmentPriority Priority { get; set; }
    }

    public class WorkContext : DbContext
    {
        public DbSet<Assignment> Assignments { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.EnableSensitiveDataLogging();

            optionsBuilder.UseLoggerFactory(
                new LoggerFactory().AddConsole()
            );

            optionsBuilder.UseSqlServer("Server=.;Initial Catalog=efenumlookup;Integrated Security=True");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity(typeof(Assignment))
                .HasData(new object[]
                {
                    new Assignment() { Id = 15, Priority = AssignmentPriority.Critical, Type = AssignmentType.Enhancement },
                    new Assignment() { Id = 16, Priority = AssignmentPriority.Criticaler, Type = AssignmentType.Enhancement },
                    new Assignment() { Id = 17, Priority = AssignmentPriority.Criticalest, Type = AssignmentType.Enhancement }
                });

            modelBuilder.CreateEnumLookupTable(createForeignKeys: true);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new WorkContext())
            {
                var assignments = context.Assignments.ToArray();
                var assignmentTypeLookups = context.Set<EnumLookup<AssignmentType>>().ToArray();
                var assignmentPriorityLookups = context.Set<EnumLookup<AssignmentPriority>>().ToArray();

                foreach (var assignment in assignments)
                {
                    Console.WriteLine($"{assignment.Id} {assignment.Type} {assignment.Priority}");
                }
                foreach (var typeLookup in assignmentTypeLookups)
                {
                    Console.WriteLine($"{typeLookup.Name} {typeLookup.Value}");
                }
                foreach (var priorityLookup in assignmentPriorityLookups)
                {
                    Console.WriteLine($"{priorityLookup.Name} {priorityLookup.Value}");
                }
            }

            Console.ReadKey();
        }
    }

[IeuanWalker]

@Kukkimonsuta hi, using your solution i keep getting the following error

{System.InvalidOperationException: The seed entity for entity type 'EnumLookup<AddressSearchTypeEnum>' cannot be added because there was no value provided for the required property 'Id'.
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidateData(IModel model)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.Validate(IModel model)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model)
   at Microsoft.EntityFrameworkCore.Internal.SqlServerModelValidator.Validate(IModel model)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)
   at System.Lazy`1.ViaFactory(LazyThreadSafetyMode mode)
   at System.Lazy`1.ExecutionAndPublication(LazyHelper executionAndPublication, Boolean useDefaultConstructor)
   at System.Lazy`1.CreateValue()
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.EntryWithoutDetectChanges[TEntity](TEntity entity)
   at Microsoft.EntityFrameworkCore.DbContext.SetEntityState[TEntity](TEntity entity, EntityState entityState)
   at LocationAzureApi.Services.AddressSearch.AddressSearchService.AddRecord(AddressSearchRecord record) in C:\WS\TS\LocationAPI\LocationAzureApi\Services\AddressSearch\AddressSearchService.cs:line 18}

[IeuanWalker]

@Kukkimonsuta sorted the above error. Originally i wasn't setting the enum id and the first enum id will be 0 which was causing the error.

BUT now i'm not getting any error and the enum tables are not being created.

UPDATE

Ran a add-migration and it detected 1 of the enums but not the other 2?

UPDATE

Ok everything is working now. The issue was the other enums where optional
Great solution thanks :)

[Dresel]

Based on @Kukkimonsuta idea we have added this to our EF Core Extension package that might be of use for others. It can be used for enum lookup table creation / seeding and table / column / key naming configuration.

This can be used within OnModelCreating:

// Enum configuration
modelBuilder.ConfigureEnumLookup(
    EnumLookupOptions.Default
    .Singularize()
    .UseNumberAsIdentifier());

// Additional table / column naming configuration
modelBuilder.ConfigureNames(
    NamingOptions.Default.Singularize()
    .SetNamingScheme(NamingScheme.SnakeCase)
    .SkipTableNamingForGenericEntityTypes());

It is available via NuGet:

Install-Package SpatialFocus.EntityFrameworkCore.Extensions