Lookup tables with Entity Framework Code First and Flags

There are a number of options for lookup tables, it is common to have the lookup values duplicated in code as an enumeration. This makes the code more readable and prevents invalid values being used.

Lookup tables themselves can also be quite laborious since they usually follow the same basic key value structure. In larger projects the database can be quickly overrun with tables just for lookup values.

The following is not an example of a production solution, there are a number of issues with it, but to get a project up and running quickly perhaps as a proof of concept or for small scale quick and dirty projects it can be useful.

In an Entity Code first scenario ideally we want to keep the classes as they would be written when ignorant of the database. The obvious choice for lookup values is to use an enumeration. Consider the following basic class:

public class Person 
{
    public Titles Title { get; set; }
    public Roles Role { get; set; }
    public string Firstname { get; set; }
    public string Lastname { get; set; }
}

public enum Titles
{
    Mrs, 
    Mr, 
    Miss, 
    Dr
}

public enum Roles
{
    Sales, 
    Accounts, 
    Customer
}

Since this is code first a context is also required, ensure the query string name either matches the context name or string value passed into the constructor:

public class MyContext : DbContext
{
    public DbSet<Person> People { get; set; }
}

On first context query the database will be generated, the resulting People table however will be missing the enumeration fields for Title and Role.

Flags Attribute

The Flags attribute has been around since the early days of .NET and code first supports enumerations that are decorated with it. There are a few changes required to make sure things work as expected.

Flags work on bitwise operations so each enumeration constant must be a power of 2:

[Flags]
public enum Titles
{
    Mrs = 1, 
    Mr = 2, 
    Miss = 4, 
    Dr = 8
}

Even if a Flags property is marked as nullable the context will still make the field NOT NULL, in this case if no option is valid then a not set enumeration value is required.

[Flags]
public enum Roles
{
    None = 0,
    Sales = 1, 
    Accounts = 2, 
    Customer = 4
}

Running the code again and the Title and Role fields are added as int fields in the db. Setting a single flag in code is done as normal:

person.Role = Roles.Sales;

Because it is just an int field in the db and the flags have been setup as powers of 2 a bitwise operator can be used to set multiple values. This means no additional lookup or linking tables are required:

person.Role = Roles.Sales | Roles.Accounts;

Querying in code can then be done with the HasFlag method

person.Role.HasFlag(Roles.Sales);

Each value must be a power of 2 so they are limited to 32 values. It is possible to increase this by extending Int64 to give 64 possible values:

[Flags]
public enum MyEnum : Int64
{
    None = 0,
First = 1,
... Last = 4611686018427387903 }

Having this this many options should probably be a warning sign, not to mention the resulting very large values so a rethink on the enumeration splits would probably be required.

There are some further limitations to using flags:

  • No db constraint to block invalid values since any integer is valid as far as the db is concerned.
  • No way to enforce a single value.
  • Inserting or removing options at a later date is problematic
  • Because a 0 is required for not set and values must be a power of 2 the db storage requirements could be higher.

So it is clear there are a number of downsides to this method which is why they should probably be avoided for anything other than simple cases. But if you need fast development then this may be a good option.