Enum generation from table data

There are two ways to do this

1. Manual way

Given the following tables

SELECT TypeName, TypeId FROM EnumTest.DaysOfWeek;

TypeName	TypeId
Sun	0
Mon	1
Tue	2
Wed	3
Thu	4
Fri	6
Sat	7

SELECT enum_name, value FROM car_options;

enum_name	value
SunRoof	0x01
Spoiler	0x02
FogLights	0x04
TintedWindows	0x08

Using these settings in your <database>.tt file

Settings.Enumerations = new List<EnumerationSettings>
{
    // Example
    new EnumerationSettings
    {
        Name       = "DaysOfWeek",          // Enum to generate. e.g. "DaysOfWeek" would result in "public enum DaysOfWeek {...}"
        Table      = "EnumTest.DaysOfWeek", // Database table containing enum values. e.g. "DaysOfWeek"
        NameField  = "TypeName",            // Column containing the name for the enum. e.g. "TypeName"
        ValueField = "TypeId"               // Column containing the values for the enum. e.g. "TypeId"
    },
    new EnumerationSettings
    {
        Name       = "CarOptions",
        Table      = "car_options",
        NameField  = "enum_name",
        ValueField = "value"
    }

Code will be generated as:

public enum DaysOfWeek
{
    Sun = 0,
    Mon = 1,
    Tue = 2,
    Wed = 3,
    Thu = 4,
    Fri = 6,
    Sat = 7,
}

public enum CarOptions
{
    SunRoof       = 0x01,
    Spoiler       = 0x02,
    FogLights     = 0x04,
    TintedWindows = 0x08,
}

2. Programmatic way

This can create the enumerations from database tables during the reverse engineering process. It is done via the AddEnum callback. In order to use the AddEnum function, Settings.ElementsToGenerate must contain both Elements.Poco and Elements.Enum elements.

public static Action<Table> AddEnum = delegate (Table table)
{
    if (table.HasPrimaryKey && table.PrimaryKeys.Count() == 1 && table.Columns.Any(x => x.PropertyType == "string"))
    {
        // Example: choosing tables with certain naming conventions for enums. Please use your own conventions.
        if (table.NameHumanCase.StartsWith("Enum", StringComparison.InvariantCultureIgnoreCase) ||
            table.NameHumanCase.EndsWith  ("Enum", StringComparison.InvariantCultureIgnoreCase))
        {
            try
            {
                Enumerations.Add(new EnumerationSettings
                {
                    Name       = table.NameHumanCase.Replace("Enum", "") + "Enum",
                    Table      = table.Schema.DbName + "." + table.DbName,
                    NameField  = table.Columns.First(x => x.PropertyType == "string").DbName, // Or specify your own
                    ValueField = table.PrimaryKeys.Single().DbName // Or specify your own
                });

                // This will cause this table to not be reverse-engineered.
                // This means it was only required to generate an enum and can now be removed.
                table.RemoveTable = true; // Remove this line if you want to keep it in your dbContext.
            }
            catch
            {
                // Swallow exception
            }
        }
    }
};

The above will take a primary key as the value of the enum, and the name field will become the first property that is a string. If this is not what you want then alter the function to suit your needs.

For example in case 739, to identify enum tables by naming conventions for example it may start with REF_ or end in _LUT. The PK of this table would be the enum value and the table always has Name column which is the enum name.

The code above would become:

public static Action<Table> AddEnum = delegate (Table table)
{
    if (table.HasPrimaryKey && table.PrimaryKeys.Count() == 1 && table.Columns.Any(x => x.PropertyType == "string"))
    {
        if (table.NameHumanCase.StartsWith("REF_", StringComparison.InvariantCultureIgnoreCase) ||
            table.NameHumanCase.EndsWith  ("_LUT", StringComparison.InvariantCultureIgnoreCase))
        {
            try
            {
                Enumerations.Add(new EnumerationSettings
                {
                    Name       = table.NameHumanCase.Replace("REF_","").Replace("_LUT","") + "Enum",
                    Table      = table.Schema.DbName + "." + table.DbName,
                    NameField  = table.Columns.First(x => x.PropertyType == "string").DbName,
                    ValueField = "Name"
                });

                // This will cause this table to not be reverse-engineered.
                // This means it was only required to generate an enum and can now be removed.
                table.RemoveTable = true; // Remove this line if you want to keep it in your dbContext.
            }
            catch
            {
                // Swallow exception
            }
        }
    }
};