Settings.Enumerations

Turns the rows of a lookup table into a real C# enum, and the five settings that control it.

Setting	Type	Role
Settings.Enumerations	List<EnumerationSettings>	Declares which tables become enums
Settings.AddEnum	Action<Table>	Decides programmatically, per table
Settings.UpdateEnum	Action<Enumeration>	Attributes on the generated enum
Settings.UpdateEnumMember	Action<EnumerationMember>	Attributes on each member
Settings.AddEnumDefinitions	Action<List<EnumDefinition>>	Replaces a column's type with an enum
Settings.UsePascalCaseForEnumMembers	bool	Whether member names are PascalCased

All apply to EF 6 and EF Core, on every database, and all are in Database.tt.

Two different features

They are easy to confuse.

Generating an enum from table rows is Settings.Enumerations. A DaysOfWeek table with a TypeId and a TypeName column becomes:

public enum DaysOfWeek
{
    Monday = 1,
    Tuesday = 2,
    ...
}

Replacing a column's type with an enum is Settings.AddEnumDefinitions. An OrderHeader.OrderStatus column typed tinyint becomes public OrderStatusType OrderStatus { get; set; }. The enum can be one you generated, or one you wrote by hand. It covers table and view columns, and since v4.0.50 the return columns of stored procedures and table-valued functions too.

You often want both: generate the enum from the lookup table, then use it as the type of the foreign key column that points at it. Type the lookup table's key as well - see Foreign keys to a lookup table.

Settings.Enumerations

Settings.Enumerations = new List<EnumerationSettings>
{
    new EnumerationSettings
    {
        Name       = "DaysOfWeek",          // The enum to generate
        Table      = "EnumTest.DaysOfWeek", // Schema.TableName
        NameField  = "TypeName",            // Column holding the member names
        ValueField = "TypeId"               // Column holding the values
    }
};

EnumerationSettings also has:

Field	Purpose
GroupField	For a table holding several enums. Put {GroupField} in Name, e.g. "{GroupField}Enum"
DescriptionField	A column whose text becomes a [Description("...")] attribute on each member
GenerateDescriptionFromName	Generates a [Description] from the member name where DescriptionField gives nothing
AllFields	Every column of the row, for use in UpdateEnumMember

Full worked examples on Enum Generation from Table Data.

Settings.AddEnum

Rather than listing tables by hand, decide by rule:

Settings.AddEnum = delegate(Table table)
{
    if (table.HasPrimaryKey &&
        table.PrimaryKeys.Count() == 1 &&
        table.NameHumanCase.EndsWith("Enum", StringComparison.InvariantCultureIgnoreCase))
    {
        Settings.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,
            ValueField = table.PrimaryKeys.Single().DbName
        });

        table.RemoveTable = true; // Do not also generate a POCO for it
    }
};

Settings.ElementsToGenerate must contain both Elements.Poco and Elements.Enum for this to work.

Settings.AddEnumDefinitions

Each EnumDefinition names a column and the enum to type it as:

Settings.AddEnumDefinitions = delegate(List<EnumDefinition> enumDefinitions)
{
    // One column on one table
    enumDefinitions.Add(new EnumDefinition { Schema = Settings.DefaultSchema, Table = "OrderHeader", Column = "OrderStatus", EnumType = "OrderStatusType" });

    // Every column called OrderStatus in the schema: tables, views, and procedure and function results
    enumDefinitions.Add(new EnumDefinition { Schema = Settings.DefaultSchema, Table = "*", Column = "OrderStatus", EnumType = "OrderStatusType" });
};

Field	Matches
Schema	The schema of the table or routine, case-insensitive. Required - there is no wildcard
Table	The table's database name or C# name, "*" for any (see The Table wildcard), or a stored procedure or function name
Column	The column's database name or C# name, case-insensitive
EnumType	The C# type to generate. Nothing checks that it exists, so a typo is a compile error in the generated code

The property is generated as EnumType, or EnumType? where the column is nullable, and a column default is cast: (OrderStatusType) 1.

Stored procedure and function return columns

A stored procedure's result set is a set of named columns, exactly like a table's, and the generator already uses those names for the properties of the ...ReturnModel class. The same definitions type them. A return model has no table to match against, so a definition applies to a return column when:

• Table is "*", or Table is the routine's name (database name or C# name), and
• Schema is the routine's schema, and
• Column matches the result column's name (as SQL reports it, or after the generator has cleaned it up into a C# identifier).

So the wildcard entry above types OrderStatus wherever it appears - OrderHeader.OrderStatus, a view that selects it, and every procedure that returns a column called OrderStatus. To type a return column without touching any table, put the routine's name in Table:

// Only the GetOrdersByStatus procedure's OrderStatus return column
enumDefinitions.Add(new EnumDefinition { Schema = Settings.DefaultSchema, Table = "GetOrdersByStatus", Column = "OrderStatus", EnumType = "OrderStatusType" });

Given this procedure and a definition scoped to it for EnumId, plus a "*" definition for AlternateEnumId:

CREATE PROCEDURE EnumTest.GetDaysOfWeek AS
BEGIN
    SET NOCOUNT ON;
    SELECT d.TypeId AS EnumId, CAST(NULL AS INT) AS AlternateEnumId, d.TypeName
    FROM EnumTest.DaysOfWeek d
    ORDER BY d.TypeId;
END

the return model is:

public class EnumTest_GetDaysOfWeekReturnModel
{
    public DaysOfWeek EnumId { get; set; }
    public DaysOfWeek? AlternateEnumId { get; set; }
    public string TypeName { get; set; }
}

Nullability follows what SQL Server reports for the result column, so a CAST(NULL AS INT) or a column from the nullable side of an outer join becomes DaysOfWeek?. Every result set of a multi-result procedure is covered, and table-valued functions go through the same path. EF Core materialises the enum from the integer column by convention; nothing extra is emitted in OnModelCreating.

Three rules apply only to return columns:

• Only integral columns are retyped. A wildcard that happens to match a varchar return column is ignored, because EF could never read a string into the enum. Table columns get no such check - see The Table wildcard.
• A column with no name cannot be matched. SELECT COUNT(*) has no name for SQL Server to report; alias it in the procedure.
• A type set by hand wins. Settings.ReadStoredProcReturnObjectCompleted runs first, and a column whose ExtendedProperties["EnumType"] it has set is left alone by the definitions.

The Table wildcard

Table = "*" means "this column name, anywhere in the schema". It is the only wildcard. Schema and Column are always compared as names, so Schema = "*" silently matches nothing - unlike Settings.AddJsonColumnMappings, where it means every schema.

Every definition is tried against two kinds of column, with slightly different rules:

	Table and view columns	Procedure and function return columns (v4.0.50+)
Schema	The table's schema	The routine's schema
Table	"*", or the table's database or C# name	"*", or the routine's database or C# name
Column	The column's database or C# name	The result column's name, or the C# name the generator made of it
Column type	Not checked - any column of that name is retyped	Integral columns only; any other is skipped
Applied by	Settings.ApplyEnumTypeReplacement, from the shipped Settings.UpdateColumn	The generator itself, whatever UpdateColumn does
Also	A column default gets the cast, (OrderStatusType) 1	A type set in ReadStoredProcReturnObjectCompleted wins

Every comparison ignores case.

The first matching entry wins. Entries are tried in the order they were added, so a "*" entry added first shadows every more specific entry after it. Add the specific ones first:

// OrderHeader.StatusId is an OrderStatusType...
enumDefinitions.Add(new EnumDefinition { Schema = "dbo", Table = "OrderHeader", Column = "StatusId", EnumType = "OrderStatusType" });

// ...and every other StatusId is a RecordStatus. Swap these two lines and OrderHeader.StatusId becomes a RecordStatus too
enumDefinitions.Add(new EnumDefinition { Schema = "dbo", Table = "*", Column = "StatusId", EnumType = "RecordStatus" });

A wildcard stops at its schema. To cover several schemas, add an entry for each:

foreach (var schema in new[] { "dbo", "sales" })
    enumDefinitions.Add(new EnumDefinition { Schema = schema, Table = "*", Column = "OrderStatus", EnumType = "OrderStatusType" });

On tables, a wildcard does not look at the column's type. Return columns are protected; table and view columns are not. Table = "*", Column = "Status" retypes a varchar Status column elsewhere in the schema just as readily as the tinyint one you meant. Generation succeeds, and then:

• EF 6 - the generated configuration does not compile. The column keeps its text settings, such as .HasMaxLength(50), and EF 6 has no such method on an enum property.
• EF Core - it compiles and the model builds, because EF Core quietly converts between an enum and a text column by member name. With Shipped = 2, the text Shipped, shipped and 2 all read as OrderStatusType.Shipped, and saving it writes Shipped. The first row holding anything else throws: InvalidOperationException: Cannot convert string value 'Dispatched' from the database to any value in the mapped 'OrderStatusType' enum.

Keep wildcards for column names that are integers wherever they appear, or name the tables instead.

Existing wildcard entries reach return columns from v4.0.50. Before v4.0.50 a "*" entry typed table and view columns only. After upgrading, an integral return column of the same name becomes the enum as well, so code that reads it as an int needs a cast. To keep a return model as it was, replace the "*" entry with an entry per table: a return column is only ever matched by "*" or by its own routine's name.

Foreign keys to a lookup table

The commonest definition is the foreign key to a lookup table: OrderHeader.OrderStatusId points at OrderStatus.OrderStatusId, and the enum is generated from OrderStatus (see Enum Generation from Table Data). While OrderStatus is still generated as an entity, type both ends of the key. Type only the foreign key and the relationship breaks:

• EF 6 - the relationship is left out, with no warning. Neither class gets its navigation property and the configuration does not map it, because EF 6 needs both ends of a foreign key to be the same type.
• EF Core - the relationship is generated, and EF Core rejects the model the first time the context is used: The relationship from ... with foreign key properties {'OrderStatusId' : OrderStatusType} cannot target the primary key {'OrderStatusId' : int} because it is not compatible.

When both ends have the same column name, one wildcard entry types both:

// OrderHeader.OrderStatusId and OrderStatus.OrderStatusId
enumDefinitions.Add(new EnumDefinition { Schema = "dbo", Table = "*", Column = "OrderStatusId", EnumType = "OrderStatusType" });

When the names differ, add an entry for each end:

// EnumTest.OpenDays.EnumId references EnumTest.DaysOfWeek.TypeId
enumDefinitions.Add(new EnumDefinition { Schema = "EnumTest", Table = "OpenDays",   Column = "EnumId", EnumType = "DaysOfWeek" });
enumDefinitions.Add(new EnumDefinition { Schema = "EnumTest", Table = "DaysOfWeek", Column = "TypeId", EnumType = "DaysOfWeek" });

A lookup table removed with table.RemoveTable = true has no entity and so no relationship: type the foreign key alone.

UpdateEnum and UpdateEnumMember

Settings.UpdateEnum       = e => e.EnumAttributes.Add("[DataContract]");
Settings.UpdateEnumMember = m =>
{
    m.Attributes.Add("[EnumMember]");

    // AllValues holds every column of the source row
    if (m.AllValues.ContainsKey("SortOrder"))
        m.Attributes.Add(string.Format("[Display(Order = {0})]", m.AllValues["SortOrder"]));
};

Settings.UsePascalCaseForEnumMembers

Default true. Member names come from data, and data is rarely written in C# style - "IN PROGRESS", "on_hold". With this on they become InProgress and OnHold; with it off they are used as-is, with illegal characters stripped.

It is separate from Settings.UsePascalCase precisely because you might want your tables PascalCased and your enum members left exactly as the data has them.

Gotchas

Enum generation reads rows, so it needs a live database. The efrpg tool is invoked a second time to select the values. That is the only part of generation that reads data rather than schema, and it will not work against a database you can only read metadata from.

Set table.RemoveTable = true or you get both. A table that becomes an enum will otherwise also generate a POCO and a DbSet, which is almost never wanted.

Table is "Schema.TableName", one string. Omitting the schema works only for the default schema.

Duplicate or illegal member names break the build. Two rows whose names PascalCase to the same identifier, or a name that is a C# keyword, produce an enum that does not compile. Data changes and your build breaks - which is an argument for DescriptionField over creative naming.

Values must be whole numbers. ValueField has to be an integral column. A string value field works only where the strings parse as integers.

A "*" definition is first-match, stops at its schema, and ignores type on tables. Read The Table wildcard before adding one.

AddEnumDefinitions needs Settings.ApplyEnumTypeReplacement to be called - for table columns. The shipped Settings.UpdateColumn calls it at the end. Replace UpdateColumn and drop that call, and the type replacement silently stops happening for tables and views. Stored procedure return columns do not go through UpdateColumn, so they keep working either way - which is a confusing place to end up, with the procedure typed and the table it reads from not.

See also

• Enum Generation from Table Data - the full walkthrough with generated output
• Settings.UpdateColumn - where ApplyEnumTypeReplacement is called
• Settings.ElementsToGenerate - Elements.Enum
• Settings Reference