Automatically Create an Enum Based on Values in a Database Lookup Table

Build Enum from database query

So your function should return a list of enums:

private List<DrugColor> BuildEnum()

Thats the first thing.
When you declar the list you return, change it to a list of enum too:

List<DrugColor> EnumList = new List<DrugColor>();

When you take the data from the database, each read() is a color so color name is a DrugColor

DrugColor colorName;
colorName = new DrugColor{(DrugColor)Enum.Parse(typeof(DrugColor ),rdr["Color"].ToString()); }

With this you parse the string into the enum.

And then you add it to the list

Voila!

EDIT:

Your enum should contain things:

enum DrugColor
{
  Red,
  Blue,
  Yellow
}

Code First Enumerations put into Lookup Tables

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.

Populate an enum with values from database

No. Enums are always fixed at compile-time. The only way you could do this would be to dyamically generate the relevant bytecode.

Having said that, you should probably work out which aspects of an enum you're actually interested in. Presumably you weren't wanting to use a switch statement over them, as that would mean static code and you don't know the values statically... likewise any other references in the code.

If you really just want a map from String to Integer, you can just use a Map<String, Integer> which you populate at execution time, and you're done. If you want the EnumSet features, they would be somewhat trickier to reproduce with the same efficiency, but it may be feasible with some effort.

So, before going any further in terms of thinking about implementation, I suggest you work out what your real requirements are.

(EDIT: I've been assuming that this enum is fully dynamic, i.e. that you don't know the names or even how many values there are. If the set of names is fixed and you only need to fetch the ID from the database, that's a very different matter - see Andreas' answer.)

Dynamically create an enum

Probably, you should consider using a Dictionary<string, int> instead.

If you want to generate the enum at compile-time dynamically, you might want to consider T4.

Creating an enum/class from a Database Table

You will have to generate an assembly if you want to be able to use the enumeration or class at compilation time. Reflection happens at execution time so that won't give you intellisense.

This is a common problem - there are a set of distinct values in a database table and those values don't change often so they are modeled as an enum in the source code. This allows these somewhat static values to be easily used in a very readable way. The problem is that when the values do change, it would be nice if the enum changed as well.

The problem with trying to keep the enum and database in sync is that an automatic process doesn't change the fact that if you are changing the database it would be very unlikely that you would do so without having to roll new code to leverage the changed value. It is better to model these values as an enum and still store them in the database. Just manually sync them as the need arises.

Can I create an enum that has more than 500 members?

Can I create an enum in C# (.net) that has 500 members

Sure, if you want to.

But I think it's a bad idea. Imagine if someone else wants to use your Database, they'll then have to have a copy of the same enum, which means you'll have to maintain 2 (or more) copies of the same list as long as other people need it and I don't think anyone wants to do that.

500 rows in a Database is like nothing, making it into an enum won't help you that much (at least not something you can notice), instead just store them in a Table and everyone is happy.

Is there a way to localize the values in enums

No, not out-of-the-box. You will have to create your own solution for this. It depends on where and how your localizations are stored.

Generate Enum from Lookup Tables - EF 6

One way is with a T4 Template that will automatically generate your Enum's from the table.

Here is a great template that I've used in a couple projects. Just change the connection string, and make sure the scripts assumptions about the format of your data are correct (i.e. that the id's are named TableNameID). Then add in the appropriate .tt files for each Enum, and run the template.

---

Related Topics