How to store a large flags enum to a single column in a SQL database?

Question

I have this enum :

[Flags]
public enum Actions 
{
    None = 0,
    MoveUp = 1,
    MoveDown = 2,
    MoveRight = 3,
    MoveLeft = 4
}

and I would like have the columns of a table like this:

Id | UserId | Actions
.. | ..     | ..

I'm confused as to how I can store the enum since the user can have more than one action, and I don't want to use another table to store actions. I have more than 50 actions, which is why I used the Flags attribute.

actionssUser = Actions.MoveUp | Actions.MoveRight | ....... 

Answer 1

First off, it's important to note that your flags enum is not properly configured, and is almost certainly not behaving the way you expect. As stated in the documentation:

Define enumeration constants in powers of two, that is, 1, 2, 4, 8, and so on. This means the individual flags in combined enumeration constants do not overlap.

The last sentence is of particular importance here, as you'll see in a moment.

Example

Let's put that into practice using your example. Your enum should instead look like:

[Flags]
public enum Actions 
{
    None            = 0,
    MoveUp          = 1,
    MoveDown        = 2,
    MoveRight       = 4,
    MoveLeft        = 8
}

Now, let's say, you set your enum value to include the MoveUp and MoveRight flags:

var actions = Actions.MoveUp | Actions.MoveRight;

You can now convert it to an integer using a simple cast:

var actionsValue = (int)actions;

In this example, that will return 5. At this point, you can simply store that value in SQL Server as a standard e.g. TINYINT column (assuming you have eight or fewer options), as @Charlieface noted in the comments.

Or, even better yet, you can cast it as a byte, and store it as a BINARY(4) column—or a BINARY(50) with your full enum—as @meysam-asadi suggests:

var actionsValue = (byte)actions;

Explanation

If you look at the above values, the only possible combination that can ever return 5 when you are using the powers of 2 is 1 (MoveUp) and 4 (MoveRight).

If you understand binary, this is even more intuitive as your bit array will look like the following:

0101

Or, from right to left:

Bit    Int    Label
1      1      MoveUp
0      2      MoveDown
1      4      MoveRight
0      8      MoveLeft

Basically, each subsequent power of two will flip the next digit from 0 to 1, marking that option as flagged.

Resurrecting your flags

On the return trip, the process looks much the same, just in reverse. So when you retrieve the value from the database, you can simply cast it back to an enum:

var actions = (Actions)actionsValue;

(Where actionsValue is whatever value you retrieve from the database.)

Limitations

There are limitations here! If you use this approach, you need to be certain that your values are stable. If you attempt to inject a value into your existing enum, you'll need to recalculate all of your previous records accordingly. To avoid this, you'll need to add any new values to the end of your enum. If this isn't an acceptable limitation, you're probably better off storing these values in individual columns.

Shorthand

This is an aside, but as you have over fifty enum values, a shorthand for assigning powers of two that’s easier to count:

[Flags]
public enum Actions 
{
    None            = 0,          // 0
    MoveUp          = 1,          // 1
    MoveDown        = 1 << 1,     // 2
    MoveRight       = 1 << 2,     // 4
    MoveLeft        = 1 << 3,     // 8
    … 
}

This is easier than trying to write out e.g., 2^50 as 1125899906842624