Fluent NHibernate Map Enum as Lookup Table

Question

I have the following (simplified)

public enum Level
{
    Bronze,
    Silver,
    Gold
}

public class Member
{
    public virtual Level MembershipLevel { get; set; }
}

public class MemberMap : ClassMap<Member>
{
    Map(x => x.MembershipLevel);
}

This creates a table with a column called MembershipLevel with the value as the Enum string value.

What I want is for the entire Enum to be created as a lookup table, with the Member table referencing this with the integer value as the FK.

Also, I want to do this without altering my model.

Answer 1

To map an enum property as an int column, use method CustomType.

public class MemberMap : ClassMap<Member>
{
    Map( x => x.MembershipLevel ).CustomType<int>();
}

In order to keep the enum and lookup table in sync, I would add the lookup table and data to your sql scripts. An integration test can verify that the enum and lookup table values are the same.

If you wanted SchemaExport to create this table, add a class and mapping for it.

public class MembershipLevel
{
    public virtual int Id { get; set; }
    public virtual string Code { get; set; }
}

public class MembershipLevelMap : ClassMap<MembershipLevel>
{
    Id( x => x.Id );
    Map( x => x.Code );
}

If you are creating the table with SchemaExport, you will need to populate it as well:

foreach (Level l in Enum.GetValues( typeof( Level ))) {
    session.Save( new MembershipLevel{ Id = (int) l, Code = l.ToString() });
}

Answer 2

I wouldn't do that because your Enum declaration is not dynamic, or simpler, it doesn't change without recompiling, while your lookup table may change at any moment. If the Enum's and lookup table's values don't match, what's next?

Another reason is if you change the Enum (in code), you'd have to synchronise it with the database table. Since Enums don't have an incremental key (PK), they can't be synchronised so simple. Let's say you remove one Enum member from your code and recompile it, what is supposed to happen? And if you change a value?

I hope I made my objections to this approach clear. So I strongly recommend storing the name or the value of your enum members. To store it by name, just map like this:

public class MemberMap : ClassMap<Member>
{
    Map(x => x.MembershipLevel, "level")
        .CustomType<GenericEnumMapper<Level>>()
        .Not.Nullable();
}

To store the values, do as @Lachlan posted in his answer.

Or if you really need a lookup table and wants to use an Enum with strict checking, create a normal model with PK (or use value for this), KEY and VALUE. Create your enum with your static members, and make the application query the database for the names and values when you start it. If things don't match, do whatever you need. Additionally, this doesn't guarantee your table won't change while your program is running, so you better be sure it doesn't.