Best method to store Enum in Database

Question

What is the best method of storing an Enum in a Database using C# And Visual Studio and MySQL Data Connector.

I am going to be creating a new project with over 100 Enums, and majority of them will have to be stored in the database. Creating converters for each one would be a long winded process therefore I'm wondering if visual studio or someone has any methods for this that I haven't heard off.

Answer 1

    [Required]
    public virtual int PhoneTypeId
    {
        get
        {
            return (int)this.PhoneType;
        }
        set
        {
            PhoneType = (PhoneTypes)value;
        }
    }
    [EnumDataType(typeof(PhoneTypes))]
    public PhoneTypes PhoneType { get; set; }

public enum PhoneTypes
{
    Mobile = 0,
    Home = 1,
    Work = 2,
    Fax = 3,
    Other = 4
}

Works like a charm! No need to convert (int)Enum or (Enum)int in code. Just use the enum and ef code first will save the int for you. p.s.: "[EnumDataType(typeof(PhoneTypes))]" attribute is not required, just an extra if you want additional functionality.

Alternatively you can do:

[Required]
    public virtual int PhoneTypeId { get; set; }
    [EnumDataType(typeof(PhoneTypes))]
    public PhoneTypes PhoneType
    {
        get
        {
            return (PhoneTypes)this.PhoneTypeId;
        }
        set
        {
            this.PhoneTypeId = (int)value;
        }
    }

Answer 2

We store ours as ints or longs and then we can just cast 'em back and forth. Probably not the most robust solution, but that what we do.

we are using typed DataSets, so, for example:

enum BlockTreatmentType 
{
    All = 0
};

// blockTreatmentType is an int property
blockRow.blockTreatmentType = (int)BlockTreatmentType.All;
BlockTreatmentType btt = (BlockTreatmentType)blockRow.blocktreatmenttype;

Answer 3

If you need store in DB string values of enum field, better do like show below. For example, it can be needed if you are using SQLite, which don`t support enum fields.

[Required]
public string PhoneTypeAsString
{
    get
    {
        return this.PhoneType.ToString();
    }
    set
    {
        PhoneType = (PhoneTypes)Enum.Parse( typeof(PhoneTypes), value, true);
    }
}

public PhoneTypes PhoneType{get; set;};

public enum PhoneTypes
{
    Mobile = 0,
    Home = 1,
    Work = 2,
    Fax = 3,
    Other = 4
}

Answer 4

If you want a store of all of your enums values, you can try the below tables to store enums and their members, and the code snippet to add those values. I'd only do this at install time, however, since those values will never change until you recompile!

DB Table:

   create table EnumStore (
    EnumKey int NOT NULL identity primary key,
    EnumName varchar(100)
);
GO

create table EnumMember (
    EnumMemberKey int NOT NULL identity primary key,
    EnumKey int NOT NULL,
    EnumMemberValue int,
    EnumMemberName varchar(100)
);
GO
--add code to create foreign key between tables, and index on EnumName, EnumMemberValue, and EnumMemberName

C# Snippet:

void StoreEnum<T>() where T: Enum
    {
        Type enumToStore = typeof(T);
        string enumName = enumToStore.Name;

        int enumKey = DataAccessLayer.CreateEnum(enumName);
        foreach (int enumMemberValue in Enum.GetValues(enumToStore))
        {
            string enumMemberName = Enum.GetName(enumToStore, enumMemberValue);
            DataAccessLayer.AddEnumMember(enumKey, enumMemberValue, enumMemberName);
        }
    }

Answer 5

In the end you will need a great way to deal with repetitious coding tasks such as enum converters. You can use a code generator such as MyGeneration or CodeSmith among many others or perhaps an ORM mapper like nHibernate to handle everything for you.

As for the structure... with hundreds of enums I would first consider trying to organize the data into a single table that might look something like this: (pseudo sql)

MyEnumTable(
EnumType as int,
EnumId as int PK,
EnumValue as int )

that would allow you to store your enum info in a single table. EnumType could also be a foreign key to a table that defines the different enums.

Your biz objects would be linked to this table via EnumId. The enum type is there only for organization and filtering in the UI. Utilizing all of this of course depends on your code structure and problem domain.

Btw, in this scenario you would want to set a clustered index on EnumType rather than leaving the default cluster idx that is created on the PKey.

Answer 6

Some things you should take in consideration.

Is the enumeration column going to be used directly by other applications like for example reports. This will limit the possibility of the enumeration being stored in it's integer format because that value will have no meaning when present in a report unless the reports have custom logic.

What are the i18n needs for your application? If it only supports one language you can save the enumeration as text and create a helper method to convert from a description string. You can use [DescriptionAttribute] for this and methods for the conversion can probably be found by searching SO.

If on the other hand you have the need to support multiple language and external application access to your data you can start considering if enumeration are really the answer. Other option like lookup tables can be considered if the scenario is more complex.

Enumerations are excellent when they are self contained in code... when they cross that border, things tend to get a bit messy.

---

Update:

You can convert from an integer using Enum.ToObject method. This implies that you know the type of the enumeration when converting. If you want to make it completely generic you need to store the type of the enumeration alongside it's value in the database. You could create data dictionary support tables to tell you which columns are enumerations and what type are them.

Answer 7

You don't need to do anything if you want to store ints. Just map your property in EF. If you want to store them as strings use converter.

Int (db type is smallint):

public override void Configure(EntityTypeBuilder<MyEfEntity> b)
{
    ...
    b.Property(x => x.EnumStatus);
}

String (db type is varchar(50)):

public override void Configure(EntityTypeBuilder<MyEfEntity> b)
{
    ...
    b.Property(x => x.EnumStatus).HasConversion<EnumToStringConverter>();
}

If you want to save your db data usage use smallint as a column in db. But data won't be human readable and you should set an index against every enum item and never mess with them:

public enum EnumStatus
{
    Active = 0, // Never change this index
    Archived = 1, // Never change this index
}

If you want to make data in db more readable you can save them as strings (e.g. varchar(50)). You don't have to worry about indexes and you just need update strings in db when you change enum names. Cons: column size gets data usage more expensive. It means if you got a table within 1,000,000 rows it might have an impact on db size and performance.

Also as a solution you can use short enum names:

public enum EnumStatus
{
    [Display(Name = "Active")]
    Act,
    [Display(Name = "Archived")]
    Arc,
}

Or use your own converter to make names in db shorter:

public enum EnumStatus
{
    [Display(Name = "Active", ShortName = "Act")]
    Active,
    [Display(Name = "Archived", ShortName = "Arc")]
    Archived,
}
...
public override void Configure(EntityTypeBuilder<MyEfEntity> b)
{
    ...
    b.Property(x => x.EnumStatus).HasConversion<MyShortEnumsConverter>();
}

More info can be found here:

EF5: https://learn.microsoft.com/en-us/ef/ef6/modeling/code-first/data-types/enums

EF6: https://learn.microsoft.com/en-us/ef/core/modeling/value-conversions

Answer 8

I'm not sure if it is the most flexible, but you could simply store the string versions of them. It is certainly readable, but maybe difficult to maintain. Enums convert from strings and back pretty easily:

public enum TestEnum
{
    MyFirstEnum,
    MySecondEnum
}

static void TestEnums()
{
    string str = TestEnum.MyFirstEnum.ToString();
    Console.WriteLine( "Enum = {0}", str );
    TestEnum e = (TestEnum)Enum.Parse( typeof( TestEnum ), "MySecondEnum", true );
    Console.WriteLine( "Enum = {0}", e );
}

Answer 9

Why not try separating the enums altogether from the DB? I found this article to be a great reference while working on something similar:

http://stevesmithblog.com/blog/reducing-sql-lookup-tables-and-function-properties-in-nhibernate/

The ideas in it should apply regardless of what DB you use. For example, in MySQL you can use the "enum" data type to enforce compliance with your coded enums:

http://dev.mysql.com/doc/refman/5.0/en/enum.html

Answer 10

A DB first approach can be used by creating a consistent table for each enum where the Id column name matches table name. It's advantageous to have enum values available within the database to support foreign key constraints and friendly columns in views. We are currently supporting ~100 enum types scattered throughout numerous versioned databases.

For a Code-First preference, the T4 strategy shown below could probably be reversed to write to the database.

create table SomeSchema.SomeEnumType (
  SomeEnumTypeId smallint NOT NULL primary key,
  Name varchar(100) not null,
  Description nvarchar(1000),
  ModifiedUtc datetime2(7) default(sysutcdatetime()),
  CreatedUtc datetime2(7) default(sysutcdatetime()),
);

Each table can be imported into C# using a T4 template (*.tt) script.

1. Create an "Enumeration Project". Add the .tt file shown below.
2. Create a sub-folder for each Database Schema name.
3. For each enum type, create a file whose name is SchemaName.TableName.tt. The file contents are always the same single line: <#@ include file="..\EnumGenerator.ttinclude" #>
4. Then to create/update the enums, right click on 1 or more files and "Run Custom Tool" (we don't have auto update yet). It will add/update a .cs file to the project:

using System.CodeDom.Compiler;
namespace TheCompanyNamespace.Enumerations.Config
{
    [GeneratedCode("Auto Enum from DB Generator", "10")]
    public enum DatabasePushJobState
    {     
          Undefined = 0,
          Created = 1,        
    } 
    public partial class EnumDescription
    {
       public static string Description(DatabasePushJobState enumeration)
       {
          string description = "Unknown";
          switch (enumeration)
          {                   
              case DatabasePushJobState.Undefined:
                  description = "Undefined";
                  break;

              case DatabasePushJobState.Created:
                  description = "Created";
                  break;                 
           }
           return description;
       }
    }
    // select DatabasePushJobStateId, Name, coalesce(Description,Name) as Description
    //    from TheDefaultDatabase.[SchName].[DatabasePushJobState]
    //   where 1=1 order by DatabasePushJobStateId 
 }

And finally, the somewhat gnarly T4 script (simplified from numerous workarounds). It will need to be customized to your environment. A debug flag can output messages into the C#. There is also a "Debug T4 Template" option when right clicking the .tt file. EnumGenerator.ttinclude:

<#@ template debug="true" hostSpecific="true" #>
<#@ output extension=".generated.cs" #>
<#@ Assembly Name="EnvDTE" #>
<#@ Assembly Name="System.Core" #>
<#@ Assembly Name="System.Data" #>
<#@ assembly name="$(TargetPath)" #>
<#@ import namespace="EnvDTE" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Collections" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#  
    bool doDebug = false;   // include debug statements to appear in generated output    

    string schemaTableName = Path.GetFileNameWithoutExtension(Host.TemplateFile);
    string schema = schemaTableName.Split('.')[0];
    string tableName = schemaTableName.Split('.')[1];

    string path = Path.GetDirectoryName(Host.TemplateFile);    
    string enumName = tableName;
    string columnId = enumName + "Id";
    string columnName = "Name"; 
    string columnDescription = "Description";

    string currentVersion = CompanyNamespace.Enumerations.Constants.Constants.DefaultDatabaseVersionSuffix;

    // Determine Database Name using Schema Name
    //
    Dictionary<string, string> schemaToDatabaseNameMap = new Dictionary<string, string> {
        { "Cfg",        "SomeDbName" + currentVersion },
        { "Common",     "SomeOtherDbName" + currentVersion }
        // etc.     
    };

    string databaseName;
    if (!schemaToDatabaseNameMap.TryGetValue(schema, out databaseName))
    {
        databaseName = "TheDefaultDatabase"; // default if not in map
    }

    string connectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" + databaseName + @";Data Source=Machine\Instance";

    schema = "[" + schema + "]";
    tableName = "[" + tableName + "]";

    string whereConstraint = "1=1";  // adjust if needed for specific tables

  // Get containing project
  IServiceProvider serviceProvider = (IServiceProvider)Host;
  DTE dte = (DTE)serviceProvider.GetService(typeof(DTE));
  Project project = dte.Solution.FindProjectItem(Host.TemplateFile).ContainingProject;
#>
using System;
using System.CodeDom.Compiler;

namespace <#= project.Properties.Item("DefaultNamespace").Value #><#= Path.GetDirectoryName(Host.TemplateFile).Remove(0, Path.GetDirectoryName(project.FileName).Length).Replace("\\", ".") #>
{
    /// <summary>
    /// Auto-generated Enumeration from Source Table <#= databaseName + "." + schema + "." + tableName #>.  Refer to end of file for SQL.
    /// Please do not modify, your changes will be lost!
    /// </summary>
    [GeneratedCode("Auto Enum from DB Generator", "10")]
    public enum <#= enumName #>
    {       
<#
        SqlConnection conn = new SqlConnection(connectionString);
        // Description is optional, uses name if null
        string command = string.Format(
            "select {0}, {1}, coalesce({2},{1}) as {2}" + "\n  from {3}.{4}.{5}\n where {6} order by {0}", 
                columnId,           // 0
                columnName,         // 1
                columnDescription,  // 2
                databaseName,       // 3
                schema,             // 4
                tableName,          // 5
                whereConstraint);   // 6
        #><#= DebugCommand(databaseName, command, doDebug) #><#

        SqlCommand comm = new SqlCommand(command, conn);

        conn.Open();

        SqlDataReader reader = comm.ExecuteReader();
        bool loop = reader.Read();

        while(loop)
        {
#>      /// <summary>
        /// <#= reader[columnDescription] #>
        /// </summary>
        <#= Pascalize(reader[columnName]) #> = <#= reader[columnId] #><# loop = reader.Read(); #><#= loop ? ",\r\n" : string.Empty #>
<#
        }
#>    }


    /// <summary>
    /// A helper class to return the Description for each enumeration value
    /// </summary>
    public partial class EnumDescription
    {
        public static string Description(<#= enumName #> enumeration)
        {
            string description = "Unknown";

            switch (enumeration)
            {<#
    conn.Close();
    conn.Open();
    reader = comm.ExecuteReader();
    loop = reader.Read();

    while(loop)
    {#>                 
                    case <#= enumName #>.<#= Pascalize(reader[columnName]) #>:
                        description = "<#= reader[columnDescription].ToString().Replace("\"", "\\\"") #>";
                        break;
                    <# loop = reader.Read(); #>
<#
      }
      conn.Close();
#> 
            }

            return description;
        }
    }
    /*
        <#= command.Replace("\n", "\r\n        ") #>
    */
}
<#+     
    private string Pascalize(object value)
    {
        Regex rxStartsWithKeyWord = new Regex(@"^[0-9]|^abstract$|^as$|^base$|^bool$|^break$|^byte$|^case$|^catch$|^char$|^checked$|^class$|^const$|^continue$|^decimal$|^default$|^delegate$|^do$|^double$|^else$|^enum$|^event$|^explicit$|^extern$|^$false|^finally$|^fixed$|^float$|^for$|^foreach$|^goto$|^if$|^implicit$|^in$|^int$|^interface$|^internal$|^is$|^lock$|^long$|^namespace$|^new$|^null$|^object$|^operator$|^out$|^overrride$|^params$|^private$|^protected$|^public$|^readonly$|^ref$|^return$|^sbyte$|^sealed$|^short$|^sizeof$|^stackalloc$|^static$|^string$|^struct$|^switch$|^this$|^thorw$|^true$|^try$|^typeof$|^uint$|^ulong$|^unchecked$|^unsafe$|^ushort$|^using$|^virtual$|^volatile$|^void$|^while$", RegexOptions.Compiled);

        Regex rx = new Regex(@"(?:[^a-zA-Z0-9]*)(?<first>[a-zA-Z0-9])(?<reminder>[a-zA-Z0-9]*)(?:[^a-zA-Z0-9]*)");
        string rawName = rx.Replace(value.ToString(), m => m.Groups["first"].ToString().ToUpper() + m.Groups["reminder"].ToString());

        if (rxStartsWithKeyWord.Match(rawName).Success)
            rawName =  "_" + rawName;

        return rawName;    
    }

    private string DebugCommand(string databaseName, string command, bool doDebug)
    {       
        return doDebug
            ? "        // use " + databaseName + ";  " + command + ";\r\n\r\n"
            : "";
    }   
#>

Hopefully the entity framework will someday support a combination of these answers to offer the C# enum strong typing within records and database mirroring of values.

Answer 11

If you are using EntityFrameworkCore. (mine version is 5.0.10), than directly use Fluent API like this: (This will save Home/Other in DB. create Type columns as varchar/nvarchar in your database)

In your Context file, where you are inheriting the DbContext

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
            modelBuilder
                .Entity<StudentAddress>()
                .Property(address => address.Type)
                .HasConversion(
                    value => value.ToString(),
                    value => (AddressType)Enum.Parse(typeof(AddressType), value));
}    

My Entity:

[Table("Student", Schema = "Student")]
public class Student 
{
    //...
    public AddressType Type { get; set; }
}

My Enum:

    public enum AddressType
    {
        Home,
        Other
    }

Extra Notes, not mandatory and not related to above sample: This is not required in my this scenario. But you can always create a constraint as well at DB level, wherever required.

    ALTER TABLE [Document].[Document] WITH CHECK ADD  CONSTRAINT [CHK_DocumentType] CHECK  (([DocumentType]="DOC" OR [DocumentType]="PDF" OR [DocumentType]="IMAGE" OR [DocumentType]="OTHER"))

    ALTER TABLE [Document].[Document] CHECK CONSTRAINT [CHK_DocumentType]