In past few articles, we have seen how to use entity framework core for defining database entities using data annotations and using fluent API. We have mostly used very basic column types (like INT and string mostly) or the entity type for defining a reference navigation property or collection navigation property.

In real world applications, we may have a lot other types to support, some of the properties might be saving geo-locations, while other properties saving amounts in dollars (or some other currency).

One of the interesting type is enumeration. Most of the applications define some enums. As a developer, we can find ourselves into dilemma – how to store an enum value in database ? Should it be a string (i.e. name of enum value) or should it be an integer value representing the enum value ?

Let’s discuss various aspects that would be helpful in taking the decision.

Smaller Column Size

Every data type should be optimized for storing any value from the input space.

Let’s say there is a boolean field IsDeleted, which by mistake, has been defined as int in database table. Generally boolean fields occupy only a bit, while INT datatype may take anywhere between 2 to 4 bytes (approximately) depending on the database concerned. And imagine, this field is for every record in the table.

Every extra bit causes resources – disk space and thereby money, thus increasing cost of the storage. Also, reading more bytes means extra delays in reading the data from database or writing data to database – although this delay may be very minute.

I generally prefer smaller column size. Now how is this related to Enums ?

If we decide to store enum names as strings, every character would take some bytes depending upon encoding used by database to store the data. So the column size would be max number of characters possible in enum name multiplied by number of bytes required to store a character.

On the other hand, if we decide to store enum value in database (and assuming it has INT values), it may have a date type INT, which means fixed number of bytes (any number between 2 to 4 bytes) – which is way smaller than the other approach. This size can further be optimized by using smaller numeric values if possible.

So, if we look at column size aspects, the approach of storing enum value wins against storing enum names.

Readable Querying Output

In my opinion, this aspect does not affect performance as such, but it is more related to convenience. This aspect is to answer a question – what should you see if you directly query the database (not via application) ?

If we choose to decide enum names in the database column, then the output of query is more readable – as compared to getting a column containing integer values and then scratching your head to figure out which integer value maps to which enum value.

But readability can be improved by adding a master table to store mapping of enum names and values. That way, main table can be joined with this this table, to show readable values. And instead of specifying join of two tables every time you want to query data, a view or a stored procedure can be created.

So, if you decide to store enum values, a master table to store (one for each enum) can be used to make query output readable. If there are many enum types, there would be many such master tables – one for each enum type.

Optionally, you can also choose to define a foreign key relationship to maintain integrity between master table and the table which holds the actual data from application.

Reporting Aspects

This may be of little concern for some applications, especially when the application has specific reporting module. But some application may rely on some tools which directly fetch data from database (like PowerBI). For such tools, having a master table would make things easier – as then the tool does not need to remember (or hardcode) mapping to display readable strings.

Proposed Approach

I think by now, the proposed approach should be pretty much clear. While saving any enum property:

• It should be mapped to a numeric data type (so should be stored as a number)
• For every enum, there should be a master table to hold mapping between strings and enum values. If not master table, then a view or a stored procedure can be created so that numeric values for directly querying
• Also, there can be a foreign key relationship between the master table and the table which stores actual data

In my opinion, this approach should work in most of the cases. There may still be some applications which may prefer storing string values in database and there is nothing wrong in it as long as all the aspects (costs vs benefits) have been carefully examined.

EF Core Example

This section shows how EF core model can be used to save enums. Before looking at code, let’s first understand the concept of value converters.

Value Converters

A value converter is a logic that allows the values to be converted from one form to another while reading from database or while writing to the database. A converter may convert values from one type to any other data-type (e.g. enum to string or int) OR it may convert value to some other value of same type.

When this value conversion happens, there are always two sides – a .NET CLR type and a Provider type (specific to database provider). Generally this conversion is specified in the form of two Func delegates – one to convert from .NET to provider specific type and other to convert from provider type to .NET CLR type.

.NET also provides some in-built converters. Some of those includes enum to string type, enum to int type, boolean to string or boolean to integer value. Full list of built-in converters can be found here in the documentation.

Code

Now, let’s say there is a user entity which has a column – MembershipType. This column is supposed to hold an enum value – any one of Premium or Free.

The EF core model should look as shown in below snapshot. Even if any additional settings are not applied, blow code would result in INT database column for membership type field.

And if we want to save this enum column as a string value, then then the conversion from enum to string can be specified via fluent API as shown in below code snippet.

Alternatively, you can also use column attribute to specify database type for the column and it would apply value conversions automatically.

Thanks for reading this article. And if you have any thoughts, do not forget to share them with me via comments.