Customizing SQL Server column types when using Entity Framework Core code first

In Entity Framework when we’re using the code first approach EF will examine our classes and using a defined mapping will create a DB structure based on them. This can result in an inefficient DB design as by default all C# properties of a certain type will be mapped to the same DB column type.

For example in Entity Framework Core 5.0 some default mappings are…

C# to SQL Server Mappings

Accepting EF default mappings across a whole DB can result in a lot of wasted space and potentially poorer performance. Thankfully there are a number of ways we can tell Entity Framework what column types we want our C# properties mapped to.

Define DB column types using data annotations

By using data annotations we can define the mappings directly in our classes immediately above each property. This means its very easy to see what type a particular property is mapped to.

From a domain driven design point of view however, data annotations may not be ideal as we’re essentially mixing in implementation details from our DB layer (SQL Server column types) with our domain objects.

As can be seen below data annotations are easy to use.

EF mappings using data annotations

Another in-class approach is to actually use different data types… so we’d use short where we want a smallint DB column and byte when we want a tinyint DB column etc. This level of granularity is not available for all data kinds though so it will only get you so far.

Define DB column types using fluent API

In Entity Framework Core the fluent API for defining property mappings is available by providing an override of the OnModelCreating method in our DbContext class. Below we can see the fluent API equivalent of the data annotations from above.

Note… I’ve included two mappings for the Decimal_Fee property just to show the new EF Core 5.0 syntax that’s now available for setting the DB precision on relevant properties.

EF to DB mappings using fluent API

Between data annotations and the fluent API I’d recommend using the fluent API as it allows us to keep all our mapping related code together and perhaps more importantly allows us to keep our domain models free of persistence related clutter.

 

Leave a Reply

Your email address will not be published. Required fields are marked *