Entity Framework database/Code First Enum support

Article
1/17/2024

Introduction

This article will perform a walkthrough on how to utilize enumerations (Enum) to retrieve data with Entity Framework 6 in a Windows Forms project for returning products by category using a modified Microsoft NorthWind database.

There are many articles on the Internet that show the very basics to perform the same operation this article demonstrates but will go farther to explain issues that other articles do not talk about which is dealing with related tables that have foreign keys on the property which a Enum will be used rather than an integer.

Since Entity Framework 5x Enum support has been added for Int32, Int64, Byte and SByte.

Usages

The first usage is when using enumerations when members are named that easily reflect something e.g. rather than 1 to represent a category for products instead member names would be English like Beverages instead of 1. Another use is passing a user selection (in this case) of categories from a ListBox, ComboBox etc. to a backend class method to return products by category without the need to perform a conversion from integer to Enum member as typically done in conventional coding.

In the image below the current need is to display products for confections by pressing the Confections button.

By using enumerations it's clear what needs to be retrieved.

/// <summary>
/// Get only Confections, easy to read
/// </summary>
/// <returns></returns>
public List<Product> GetProductsWithCategoryConfections()
{
  using (var context = new NorthWindContext())
  {
    return context.Products
      .Where(prod => prod.CategoryID == CategoriesName.Confections)
      .OrderBy(p => p.ProductName)
      .ToList();
  }
}

Creating Enum for reference table

The table for demonstration.

The first method is using conventional code as shown below.

using System;
using System.Data.SqlClient;
using System.Text;
 
namespace EnumGeneratorLibrary
{
  public class  Generator
  {
    /// <summary>
    /// Create an enum from a reference table using the primary key
    /// as a value for a enum member and a description field name for
    /// the enum name.
    /// </summary>
    /// <param name="pServerName">Server name e.g. .\SQLEXPRESS</param>
    /// <param name="pDatabase">Database containing table to create enum</param>
    /// <param name="pTable">Table name under pServerName, pDatabase to create enum</param>
    /// <param name="pIdentifier">Primary key in pTable</param>
    /// <param name="pName">Description/name of enum</param>
    /// <returns></returns>
    public string  Create(
      string pServerName, 
      string pDatabase, string pTable, 
      string pIdentifier, 
      string pName)
    {
      var builder = new  StringBuilder();
 
      var connectionString = 
        $"Data Source={pServerName};Initial Catalog={pDatabase};Integrated Security=True";
 
      using (var cn = new SqlConnection {ConnectionString = connectionString })
      {
        var selectStatement = $"SELECT {pIdentifier},{pName} FROM {pTable}";
        using (var cmd = new SqlCommand{Connection = cn, CommandText = selectStatement })
        {
          cn.Open();
          var reader = cmd.ExecuteReader();
          if (reader.HasRows)
          {
            builder.AppendLine($"public enum {pTable}");
            builder.AppendLine("{");
            while (reader.Read())
            {
              builder.AppendLine(
                $"   {reader.GetString(1).Replace(" ","")} = "  +
                $"{reader.GetInt32(0)},");
            }
 
            builder.AppendLine("};");
 
            return ReplaceLastOccurrence(builder.ToString(),",","");
          }
          else
          {
            return "";
          }
        }
      }
    }
    public string  ReplaceLastOccurrence(string pSource, string pFind, string pReplace)
    {
      var place = pSource.LastIndexOf(pFind, StringComparison.Ordinal);
 
      if (place == -1)
      {
        return pSource;
      }
 
      var result = pSource.Remove(place, pFind.Length).Insert(place, pReplace);
      return result;
    }
  }
}

Then call the method above with the server name, database name, table name, field names to generate the Enum. The code below is in a Console project.

using System;
using EnumGeneratorLibrary;
 
namespace ConsoleApp1
{
  class Program
  {
    static void  Main(string[] args)
    {
      var ops = new  Generator();
      var results = ops.Create(".\\SQLEXPRESS", 
        "NorthWindAzureForInserts", 
        "Categories", 
        "CategoryID", 
        "CategoryName");
 
      Console.WriteLine(results);
      Console.ReadLine();
 
    }
  }
}

Another method to create the desired Enum is using a T4 template. For creating an Enum using the T4 template below, first change the following variable for your table.

var columnId = "CategoryID";
var columnName = "CategoryName";
var columnDescription = "CategoryName";
var connectionString = "data source=.\\SQLEXPRESS;initial catalog=NorthWindAzureForInserts;integrated security=SSPI";

Full template

<#@ template debug="true" hostSpecific="true" #>
<#@ output extension=".cs" #>
<#@ Assembly Name="EnvDTE.dll" #>
<#@ Assembly Name="System.Data" #>
<#@ import namespace="EnvDTE" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#
  var tableName = Path.GetFileNameWithoutExtension(Host.TemplateFile);
  var path = Path.GetDirectoryName(Host.TemplateFile);
  var columnId = "CategoryID";
  var columnName = "CategoryName";
  var columnDescription = "CategoryName";
  var connectionString = "data source=.\\SQLEXPRESS;initial catalog=NorthWindAzureForInserts;integrated security=SSPI";
 
  // 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>
  /// <#= tableName #> auto generated enumeration
  /// </summary>
  [GeneratedCode("TextTemplatingFileGenerator", "10")]
  public enum <#= tableName #>
  {
<#
  SqlConnection conn = new SqlConnection(connectionString);
  string command = string.Format("select {0}, {1}, {2} from {3} order by {0}", columnId, columnName, columnDescription, tableName);
  SqlCommand comm = new SqlCommand(command, conn);
 
  conn.Open();
 
  SqlDataReader reader = comm.ExecuteReader();
  bool loop = reader.Read();
 
  while(loop)
  {
#>    
    /// <summary>
    /// <#= reader[columnName] #> 
    /// </summary>
    <#= Pascalize(reader[columnName]) #> = <#= reader[columnId] #><# loop = reader.Read(); #><#= loop ? ",\r\n" : string.Empty #>
<#
  }
#>  }
}
<#+
  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());
  }
#>

Caveat, for each reference table a separate .TT (T4) template file is needed.

Next, select the file above in Solution Explorer followed by selecting properties. In Custom Tool enter TextTemplatingFileGenerator as per the image below.

To generate a .cs file, right click on the file in solution explorer and select "Run custom tool" which generates a new file. This file can then be copied to your project. Here is the end result.

namespace NorthWindLibrary.Classes
{
  public enum  CategoriesName : int
  {
 
    /// <summary>
    /// Beverages 
    /// </summary>
    Beverages = 1,
 
    /// <summary>
    /// Condiments 
    /// </summary>
    Condiments = 2,
 
    /// <summary>
    /// Confections 
    /// </summary>
    Confections = 3,
 
    /// <summary>
    /// Dairy Products 
    /// </summary>
    DairyProducts = 4,
 
    /// <summary>
    /// Grains/Cereals 
    /// </summary>
    GrainsCereals = 5,
 
    /// <summary>
    /// Meat/Poultry 
    /// </summary>
    MeatPoultry = 6,
 
    /// <summary>
    /// Produce 
    /// </summary>
    Produce = 7,
 
    /// <summary>
    /// Seafood 
    /// </summary>
    Seafood = 8,
 
    /// <summary>
    /// Wine 
    /// </summary>
    Wine = 9
  }
}

To use this, in the following class CategoryID was an int as generated via Code First database first. CategoryID has been replaced with the enumeration above.

using NorthWindLibrary.Classes;
 
namespace NorthWindLibrary
{
  using System.Collections.Generic;
  using System.ComponentModel.DataAnnotations;
 
  public partial  class Category
  {
    [System.Diagnostics.CodeAnalysis.SuppressMessage(
      "Microsoft.Usage", 
      "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Category()
    {
      Products = new  HashSet<Product>();
    }
 
    public CategoriesName CategoryID { get; set; }
 
    [Required]
    [StringLength(15)]
    public string  CategoryName { get; set; }
 
    [System.Diagnostics.CodeAnalysis.SuppressMessage(
      "Microsoft.Usage", 
      "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual  ICollection<Product> Products { get; set; }
 
    public override  string ToString()
    {
      return CategoryName;
    }
  }
}

If the application runs and tries to retrieve data and exception will be thrown and the error may not point to what is wrong which is another class representing child tables, in this case products which has public int CategoryID { get; set; } and need to be changed to public CategoriesName CategoryID { get; set; }. Once both classes have been updated to use CategoriesName instead of int data can be worked on e.g.

/// <summary>
/// Get only Beverages, easy to read
/// </summary>
/// <returns></returns>
public List<Product> GetProductsWithCategoryBeverages()
{
  using (var context = new NorthWindContext())
  {
    return context.Products
      .Where(prod => prod.CategoryID == CategoriesName.Beverages)
      .OrderBy(p => p.ProductName)
      .ToList();
  }
}

There are time when an enumeration direct is not going to work so a method is needed to accept a enum member name.

/// <summary>
/// Get products by category, common method yet not as readable as above.
/// </summary>
/// <param name="categoriesName">
/// <see cref="CategoriesName"/> specifies which category to show</param>
/// <returns></returns>
public List<Product> GetProducts(CategoriesName categoriesName)
{
  using (var context = new NorthWindContext())
  {
    return context.Products
      .Where(prod => prod.CategoryID == categoriesName)
      .OrderBy(p => p.ProductName)
      .ToList();
  }
}

Let' look at an example for dynamically returning a specific category. Load all category names into a ComboBox.

private void  Form1_Shown(object  sender, EventArgs e)
{
  CategoryComboBox.DataSource = _operations.GetCategories();
}

Select a category name from the ComboBox followed by clicking a button which first cast the ComboBox.SelectedItem to type Category following by passing CategoryID to the method GetProducts.

Another requirement might be to get two categories.

public List<Product> GetBeveragesAndCondiments()
{
  using (var context = new NorthWindContext())
  {
    return context
      .Products
      .Where(p => p.CategoryID == CategoriesName.Beverages || 
            p.CategoryID == CategoriesName.Condiments)
      .ToList();
  }
}

Categories are not displayed as this is simply validation on who to get more than one category.

EF Core 5 Example

In this case, a web site allows changing the language e.g. English and Spanish for instance.

Using the generator this is the new Enum

public enum  LanguageCodes
{
    /// <summary>
    /// English 
    /// </summary>
    English = 1,
    /// <summary>
    /// Spanish 
    /// </summary>
    Spanish = 2
}

Which can be used in a method like the following (this code is not in source code).

public static  async Task<IccTran> GetTransactionWithCancellation(decimal id, LanguageCodes language)
{
    var cancellationTokenSource = new  CancellationTokenSource(QueryTimeout());
     
    var transItem = new  IccTran();
    var code = language.ToText();
 
    await Task.Run(async () =>
    {
        await using  var context = new  Context();
 
        transItem = await context.IccTrans.FirstOrDefaultAsync(trans => trans.Id == id && trans.TrLanguageCode == code);
 
 
    },cancellationTokenSource.Token);
     
    return transItem;
 
}

Then always test e.g.

Summary

In this article, use of enumerations (Enum) has been shown how to use against a reference table in a SQL-Server database along with what to do when there are other tables referencing this table. Also provided options for generating Enum for a reference table in a SQL-Server database.

Source code

https://github.com/karenpayneoregon/EntityFrameworkEnum

Share via