Entity Framework 5- Dynamically generating Enum from Values stored in Database

Question

So, I have an existing lookup table in my database that stores an ID column(int), a description/Name for the ID Value. We have been using the data in this table for enumeration purposes.

We have since moved to using Entity Framework 5 and CodeFirst, but our lookup values are still in the database. What we would like to do is create an entity to represent the values, preferably an enum to access the values and names in that table and make them available for use in our application.

Normally, the code below would make sense for dynamically building an enumeration type from database values, but we want to get away from doing this.

 static void GenerateStateEnumerations(String enumassemblyName, String enumTypeName, String assemblyName, IDictionary<int, String> EnumDB)
    {
        // Get the current application domain for the current thread.
        AppDomain currentDomain = AppDomain.CurrentDomain;
         // Create a dynamic assembly in the current application domain,  
         // and allow it to be executed and saved to disk.
        AssemblyName EnumassemblyName = new AssemblyName(enumassemblyName);

         AssemblyBuilder ab = currentDomain.DefineDynamicAssembly(
        EnumassemblyName, AssemblyBuilderAccess.RunAndSave);
        // Define a dynamic module in assemblyName assembly. For a single-
        // module assembly, the module has the same name as the assembly.
         ModuleBuilder mb = ab.DefineDynamicModule(EnumassemblyName.Name, EnumassemblyName.Name + ".dll");
         // Define a public enumeration with the name "Elevation" and an 
         // underlying type of Integer.
         EnumBuilder eb = mb.DefineEnum(enumTypeName, TypeAttributes.Public, typeof(int));
        // Define the enumeration members
         foreach (KeyValuePair<int, String> element in EnumDB)
         {
             eb.DefineLiteral(element.Value, element.Key);
         }
         // Create the type and save the assembly.
         Type finished = eb.CreateType();
        //Save the assembly via the assembly builder
         ab.Save(EnumassemblyName.Name + ".dll");
    }

How do I, in EF 5, create an entity that is an enumeration entity to represent the data stored in this table? Is this currently possible?

Answer 1

I know you said that you didn't want to do this dynamically, but have you looked at using text templates? If you use a text template (*.tt T4 file) The enum will be dynamically generated every time you do a build. Any new values in the table will be automatically added to the enum in the code.

Here's 1 example of how to create an enum from a text template:

<#@ template language="C#" hostSpecific="true" #>
<#@ output extension=".generated.cs" #>
<#@ Assembly Name="C:\Program Files (x86)\Common Files\microsoft shared\MSEnv\PublicAssemblies\EnvDTE.dll" #>
<#@ Assembly Name="System.Data" #>
<#@ assembly name="System.Configuration.dll" #>
<#@ Assembly Name="System.Core.dll" #>
<#@ import namespace="EnvDTE" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Configuration" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#
// This file dynamically creates Enumerations to be used in the [your project name] project.
// get an appSetting value
//string myAppSetting = AppSettings["myAppSetting"].Value;
// use a helper method to get value or default
// string absentAppSetting = (string)AppSettingValueOrDefault("xxx","default value for absent setting");
// get a project property
//string rootNamespace = Project.Properties.Item("RootNamespace").Value.ToString();

ConnectionStringSettings cs = ConnectionStrings["MyDBName"];

string tableName = "dbo.MyEnumTable"; // Path.GetFileNameWithoutExtension(Host.TemplateFile);
string path = Path.GetDirectoryName(Host.TemplateFile);
string columnId = "MyEnumTableId"; 
string columnName = "MyEnumTableValue"; 
string connectionString = cs.ConnectionString;

// Get containing project

IServiceProvider serviceProvider = (IServiceProvider)Host;
DTE dte = (DTE)serviceProvider.GetService(typeof(DTE));
Project project = dte.Solution.FindProjectItem(Host.TemplateFile).ContainingProject;
 # > //remove space between # > issue with SO formatting

using System;
using System.CodeDom.Compiler; 
using System.Collections.Generic;

namespace <#= project.Properties.Item("DefaultNamespace").Value #>
{
/// <summary>
/// Auto generated enumeration from database table <#= tableName #>
/// </summary>

[GeneratedCode("TextTemplatingFileGenerator", "10")]
public enum MyEnumTableEnum
{
<#

SqlConnection conn = new SqlConnection(connectionString);
string command = string.Empty;

command = string.Format("select tbl.{0}, tbl.{1} from {2} as tbl order by tbl.{0}", columnId, columnName, tableName);

SqlCommand comm = new SqlCommand(command, conn);

conn.Open();

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

while(loop)
{
# >  //remove space between # > issue with SO formatting      
/// <summary>
    /// <#= reader[columnName] #> configuration setting. 
    /// </summary>
    <#= Pascalize(reader[columnName]) #> = <#= reader[columnId] #><# loop = reader.Read(); #><#= loop ? ",\r\n" : string.Empty #>
<#
}
#>  


}

/// <summary>
/// This auto generated class is used to get or set the MyEnumTableEnum user friendly display names.
/// The 'values' object is of type Dictionary<int, string>
/// Usage: string keyValue = MyEnumTableDict.values[int key];
/// </summary>
public class MyEnumTableDict
{
    public static Dictionary<int, string> values = new Dictionary<int, string>();

    static MyEnumTableDict()
    {
        <#
            reader.Close();
            reader = comm.ExecuteReader();
            loop = reader.Read();

while(loop)
{
#>
        values[<#= reader[columnId] #>] = "<#= reader[columnName] #>";<#= "\r\n" #><# loop = reader.Read(); #>
<#
}
reader.Close();
reader.Dispose();
#>
    }       
}

}

<#+
/// <summary>
/// Used to Pascal-case objects
/// </summary>
private string Pascalize(object value)
{
    Regex rx = new Regex(@"(?:[^a-zA-Z0-9]*)(?<first>[a-zA-Z0-9])(?<reminder>[a-zA-Z0-9]*)(?:[^a-zA-Z0-9]*)");
    return rx.Replace(value.ToString(), m => m.Groups["first"].ToString().ToUpper() + m.Groups["reminder"].ToString().ToLower());
}

// Configuration Accessor Helpers

private static System.Configuration.Configuration _config;
private static EnvDTE.Project _project;

/// <summary>
/// Example:
/// <code>
/// string cs = ConnectionStrings["myConnectionString"].ConnectionString;
/// </code>
/// </summary>
private ConnectionStringSettingsCollection ConnectionStrings
{
   get { return Configuration.ConnectionStrings.ConnectionStrings;}
}

/// <summary>
/// Example:
/// <code>
/// string setting = AppSettings["mySetting"].Value;
/// </code>
/// </summary>
private KeyValueConfigurationCollection AppSettings
{
   get { return Configuration.AppSettings.Settings; }
}

/// <summary>
/// Returns value of setting, if present, otherwise defaultValue
/// </summary>
/// <param name="key">appSettings key</param>
/// <param name="defaultValue">value to return if setting is absent</param>
/// <returns></returns>
private object AppSettingValueOrDefault(string key,object defaultValue)
{
   if (AppSettings[key] != null)
   {
        return AppSettings[key].Value;
   }          

   return defaultValue;
}


/// <summary>
/// The app/web config file for hosting project, if any
/// </summary>
private System.Configuration.Configuration Configuration
{
    get
    {
        if(_config==null)
        {
            string configurationFilename=null;  

            // examine each project item's filename looking for app.config or web.config
            foreach (EnvDTE.ProjectItem item in Project.ProjectItems)
            {
                if (Regex.IsMatch(item.Name,"(app|web).config",RegexOptions.IgnoreCase))
                {
                    // TODO: try this with linked files. is the filename pointing to the source?
                    configurationFilename=item.get_FileNames(0);
                    break;
                }
            }

            if(!string.IsNullOrEmpty(configurationFilename))
            {
                // found it, map it and return it
                ExeConfigurationFileMap configFile = null;
                configFile = new ExeConfigurationFileMap();
                configFile.ExeConfigFilename=configurationFilename;

                _config = System.Configuration.ConfigurationManager.OpenMappedExeConfiguration(configFile, ConfigurationUserLevel.None);
            }  
        }

        return _config;
    }
}

/// <summary>
/// The hosting project.
/// </summary>
private EnvDTE.Project Project
{
    get
    {
        if(_project==null)
        {
            // Get the instance of Visual Studio that is hosting the calling file
            EnvDTE.DTE env = (EnvDTE.DTE)((IServiceProvider)this.Host).GetService(typeof(EnvDTE.DTE));
            // Gets an array of currently selected projects. Since you are either in this file saving it or
            // right-clicking the item in solution explorer to invoke the context menu it stands to reason
            // that there is 1 ActiveSolutionProject and that it is the parent of this file....
            _project = (EnvDTE.Project)((Array)env.ActiveSolutionProjects).GetValue(0);
        }

        return _project;
    }
}

#>

Update:

Forgot to include output class. Including now for completeness :)

using System;
using System.CodeDom.Compiler; 
using System.Collections.Generic;

namespace MyNameSpace
{
    /// <summary>
    /// Auto generated enumeration from database table dbo.MyEnumTable
    /// </summary>

    [GeneratedCode("TextTemplatingFileGenerator", "10")]
    public enum MyEnumTableDictEnum
    {
        /// <summary>
        /// description 1
        /// </summary>
        enumName1 = 1,

        /// <summary>
        /// description 2 
        /// </summary>
        enumName2 = 2,

        //..etc.
     }

    /// <summary>
    /// This auto generated class is used to get or set the MyEnumTableDictEnum 
    ///   user friendly display names.
    /// The 'values' object is of type Dictionary<int, string>
    /// Usage: string keyValue = MyEnumTableDict.values[int key];
    /// </summary>
    public class MyEnumTableDict
    {
        public static Dictionary<int, string> values = new Dictionary<int, string>();

        static MyEnumTableDict()
        {
                    values[1] = "EnumName1";
            values[2] = "EnumName2";
            values[3] = "...etc.";

        }       
     }
}