How to keep a C# Enum in sync with a table in database

Question

This is a somewhat simplified example (I changed it around to hide the actual code). I have a database-powered app and a small tool that is being developed separately that is meant to work with the app. There is a table that defines an enumeration, but it could potentially change over time. Suppose some app (medical?) needed to track the sex of a person rather precisely.

select * from sex order by id;

id | mnemonic | description
0  | U        | Unknown sex
1  | M        | Male
2  | F        | Female
3  | T        | Trans-gender

And my C# enum:

public enum SexType
{
    /// <summary>Unknown sex.</summary>
    [Description("U")]
    Unknown = 0,

    /// <summary>Male sex.</summary>
    [Description("M")]
    Male = 1,

    /// <summary>Female sex.</summary>
    [Description("F")]
    Female = 2

    /// <summary>Trans-gender sex.</summary>
    [Description("T")]
    TransGender = 3,
}

Here I should not assume that the id is a continuous sequence; neither are these enums flags that can be combined, even though it might look that way.

Some of the logic is done in SQL; some is done in C# code. For instance, I might have a function:

// Here we get id for some record from the database.
public static void IsMaleOrFemale(int sexId)
{
    if (!Enum.IsDefined(typeof(SexType), sexId))
    {
        string message = String.Format("There is no sex type with id {0}.", 
            sexId);
        throw new ArgumentException(message, "sexId");
    }

    var sexType = (SexType) sexId;
    return sexType == SexType.Male || sexType == SexType.Female;
}

This can work fairly well as long as neither the table nor the enum definitions change. But the table might. I cannot rely on the id column or the mnemonic column maintaining their values. I think the best I can to is have a unit test which makes sure that the table is in sync with my definition of an enum. I am trying to match the value to id, and the description attribute to mnemonic column.

So, how to I get a list of all pairs (programmatically, in C#): (0, "U"), (1, "M"), (2, "F"), (3, "T") by looking at the enum SexType?

The idea is to tightly compare this ordered list with select id, mnemonic from sex order by is asc;.

Answer 1

Why not change SexType from an enum to a class (or Struct) and populate a List from your database at runtime?

Your struct (or class) would look something like this:

public struct SexType
{
  public string Type;
  public string Code;
  public int Value;
}

Then you could populate from your database a List<SexType> (or, if you're using EF, you could pull down a list of Entities of type SexType, or whatever your solution allows).

Presuming you're using Linq and EF, do eager loading when the application starts, and you should be good-to-go.