Mapping .NET Enumerations to the Database

Introduction

One of the commonly overlooked details in common development is the utilization of .NET enumeration and translation of such values to and from a database repository. A common approach employed by a lot of developers is to write decoding logic to convert read values from a database into enumerations or the other way around. In a diverse development environment, this could lead to interpretations and incorrect coding across teams. In the following, I will try to present a reusable technique that allows mapping the .NET enumerations to and from data read from a database repository.

Using the Code

In the following sections, we will explore how to map .NET enumerations to data fields.

While working on several development projects in the past five years, I have encountered several cases where developers turned away from using .NET enumerations when it came to tag properties with data that is saved to the database. For example, if you would design an object and one of the properties would be color, a natural approach would be to enumerate the different colors. Using an enumeration in this case can simplify the coding process around this property.

public enum EColor
{
	CustomEnumKey("0")]
	None,
	[CustomEnumKey("1")]
	White,
	[CustomEnumKey("2")]
	Red,
	[CustomEnumKey("3")]
	Black,
}

You can see that the values of the EColor enumeration have a custom attribute CustomEnumKey that stores a key value. Using the CustomEnumKey custom attribute, we associate the enumeration value with a Key that we can later use to store in the database. I will discuss later some of the options available for assigning a Key value, which can vary by the storage type used for the data.

Let's explore the CustomEnumKey custom attribute:

[AttributeUsage(AttributeTargets.Field, AllowMultiple = false)]
public class CustomEnumKey: System.Attribute
{
	public CustomEnumKey(string key)
	{
		this.Key = key;
	}
	public string Key { get; private set; }
}

The CustomEnumKey enumeration provides a constructor and exposes a read only property to access the Key value. The usage of the attribute enforces that this custom attribute can only be applied to fields in a class (in our case, the enumeration values) and that only one custom attribute can be applied at the time.

Let's create a Car class that would have a Color property of type EColor:

public class Car
{
	public string Make { get; set; }

	public EColor Color { get; private set; }
}

Now that we have the Car class, let's explore how we would translate the Color property value, represented by an enumeration value, to a Key value that we would store in a data field.

Let's look at a method that translates the EColor enumeration value to the mapped Key:

public static string ParseFromEnum<T>(T enumeration)
{
	if (enumeration.GetType().IsEnum)
	{
		var q = enumeration.GetType().GetFields()
			  .Where(f => f.GetValue(enumeration).Equals(enumeration))
			  .Select(c => new
			  {
				  Key = (c.GetCustomAttributes
					 (typeof(CustomEnumKey),
					 false) as CustomEnumKey[])
					 .DefaultIfEmpty(new CustomEnumKey
					 (string.Empty))
					 .FirstOrDefault().Key
			  });

		return q.FirstOrDefault().Key.ToString();
	}
	return string.Empty;
}

The ParseFromEnum<T> generic method will retrieve the Key value for the specified enumeration value by inspecting the custom attribute collection associated with the enumeration value. If the enumeration passed onto the method doesn't have a custom attribute of type CustomEnumKey it will return String.Empty as the return value. This method could be modified to return null if the utilization of this method requires it.

The following method allows to perform the reverse translation, from a Key value to an enumeration value:

public static T ParseToEnum<T>(string key)
{
	if (default(T).GetType().IsEnum)
	{
		var q = default(T).GetType().GetFields()
				.Where(f => ((CustomEnumKey[])
				f.GetCustomAttributes(typeof(CustomEnumKey), false))
				.Where(c => c.Key.Equals(key, 
				StringComparison.OrdinalIgnoreCase)).Count() > 0)
				.Select(o => new 
				{ KeyValue = o.GetValue(default(T)) });

		return q.Count() > 0 ? (T)q.FirstOrDefault().KeyValue : default(T);
	}
	return default(T);
}

The generic method will attempt to match an enumeration value with a provided Key value. If it is unable to, it will return the default value of the enumeration. This scenario highlights the fact the current code assumes that your enumeration can have a default value, which should not have any "business" meaning. If this is not the case, different means to handle this scenario are needed.

Now let's put it all together:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace EnumParser
{
	class Program
	{
		static void Main(string[] args)
		{
			Console.WriteLine("Mockup read from Database");

			Car carFromDb = DAL.GetCarFromDatabase();

			Console.WriteLine(string.Format
				("Car Make: {0}", carFromDb.Make));
			Console.WriteLine(string.Format
				("Car Color: {0}", carFromDb.Color.ToString()));

			Console.WriteLine("");

			Console.WriteLine("Mockup save to Database");

			Car carToDb = new Car();
			carToDb.Make = "Chevrolet";
			carToDb.Color = EColor.Red; //Enumeration Key value 2
			DAL.SaveCarToDatabase(carToDb);
			
			Console.WriteLine("");
			Console.WriteLine("Press any key");
			Console.ReadKey();
		}
	}

	[AttributeUsage(AttributeTargets.Field, AllowMultiple = false)]
	public class CustomEnumKey : System.Attribute
	{
		public CustomEnumKey(string key)
		{
			this.Key = key;
		}

		public string Key { get; private set; }
	}

	public enum EColor
	{
		[CustomEnumKey("0")]
		None,
		[CustomEnumKey("1")]
		White,
		[CustomEnumKey("2")]
		Red,
		[CustomEnumKey("3")]
		Black,
	}

	public class Car
	{
		public string Make { get; set; }

		public EColor Color { get; set; }
	}

	public static class DataParser
	{

		public static string ParseFromEnum<T>(T enumeration)
		{
			if (enumeration.GetType().IsEnum)
			{
				var q = enumeration.GetType().GetFields()
					  .Where(f => f.GetValue
					    (enumeration).Equals(enumeration))
					  .Select(c => new
					  {
						  Key = (c.GetCustomAttributes
							(typeof(CustomEnumKey), 
							false) as CustomEnumKey[])
							.DefaultIfEmpty(new 
							CustomEnumKey
							(string.Empty))
							.FirstOrDefault().Key
					  });

				return q.FirstOrDefault().Key.ToString();
			}
			return string.Empty;
		}

		public static T ParseToEnum<T>(string key)
		{
			if (default(T).GetType().IsEnum)
			{
				var q = default(T).GetType().GetFields()
						.Where(f => ((CustomEnumKey[])
						f.GetCustomAttributes
						(typeof(CustomEnumKey), false))
						.Where(c => c.Key.Equals
						(key, StringComparison.
						OrdinalIgnoreCase)).Count() > 0)
						.Select(o => new { KeyValue = 
						o.GetValue(default(T)) });

				return q.Count() > 0 ? 
					(T)q.FirstOrDefault().KeyValue : default(T);
			}
			return default(T);
		}
	}

	public static class DAL
	{
		public static Car GetCarFromDatabase()
		{
			Car car = new Car();

			DataTable dt = MockDatabaseCall();

			car.Make = dt.Rows[0]["car_make"].ToString();
			car.Color = DataParser.ParseToEnum<EColor>
					(dt.Rows[0]["car_color"].ToString());

			return car;
		}

		public static void SaveCarToDatabase(Car car)
		{
			using (DataTable dt = new DataTable())
			{
				dt.Columns.Add("car_make", typeof(string));
				dt.Columns.Add("car_color", typeof(string));

				DataRow dr = dt.NewRow();
				dr["car_make"] = car.Make;
				dr["car_color"] = DataParser.ParseFromEnum
						<EColor>(car.Color).ToString();

				dt.Rows.Add(dr);

				Console.WriteLine(string.Format("Car Make: {0}", 
					dt.Rows[0]["car_make"].ToString()));
				Console.WriteLine(string.Format("Car Color: {0}", 
					dt.Rows[0]["car_color"].ToString()));
				
				//save to database code....
			}
		}

		private static DataTable MockDatabaseCall()
		{
			using (DataTable dt = new DataTable())
			{
				dt.Columns.Add("car_make", typeof(string));
				dt.Columns.Add("car_color", typeof(string));

				DataRow dr = dt.NewRow();
				dr["car_make"] = "Ford";
				dr["car_color"] = "1"; //WHITE COLOR

				dt.Rows.Add(dr);

				return dt;
			}
		}
	}
}

Currently the code implies that the Key data type is a string. For real business purposes, a better analysis should be done to determine the appropriate data type and values that should be used for the enumeration mapping. Choosing a data type that allows indexing on the database side will help address performance with queries written to retrieve the data.

Points of Interest

While there are probably several variations of how the translation methods can be implemented, the scope of this article is to present one of the most underrated possibilities available in .NET programming: translating to and from .NET enumeration field to a stored value in a data field. By creating methods to translate between code and data stored in a database, you can leverage enumerations much easier and can lead to cleaner code.

History

• 08-25-2009: Original article