EF Core Part 6: Transformations
Sometimes we want a different name for a table or a column in database than we do in the code. Sometimes we need to apply limitations on a property to better fit the database capabilities. Maybe we want to massage the database before it goes into the database. These are all transformations.
We’ll go through some of these in case they come in handy at some point. In all cases here, we need to do an
Update-Database when we’ve finished with our transformations.
What if we don’t want to name our model the same as our database table? Maybe we want to give our model a more descriptive name than
Store. Maybe we want to refer to it as
ShoppingExperience. Why? I don’t know, maybe some manager wants the code to have the same flowery language that marketing uses. So let’s do it! We’ll begin by changing the name of our model from
Next, we’ll use the Fluent API to accomplish this. So let’s crack open the
DbContext and go into that oh-so-familiar
OnModelCreating function and add the following.
The end! It’s that simple.
Let’s see how to do the same thing using attributes. We start by opening the
ShoppingExperience model up again and add the
[Table("Store")] attribute to the class. That’s it! Easy peasy.
What if we don’t like the name of a column in the database? Maybe we need to expand the name that we abbreviated in the code to have a more descriptive column name in the database. Something like…
StKey. Here comes column mapping to save the day!
Let’s roll back that model name change we did above. This way we aren’t getting our transformations intermingled. Let’s welcome back the
We need map our
StKey property to a
StoreKey column in our
Store table. We’ll stick with Fluent API first, so into the
OnModelCreating and we’ll add:
.Property(p => Store.StKey)
If you aren’t using Fluent API then you can accomplish the same thing with an attribute. Just pop over into the
Store model and add a <code[Column(“StoreKey”)] attribute to the
StKey property. All done!
Column Data Types
What if we need a different data type on a column in the database? EF Core will use
nvarchar(MAX) by default for
string types, and we don’t want to allow for up to 2GB of text in that column. Maybe we want a more manageable 100 character limit. Let’s stick with our original
Store model and see how we can handle these transformations.
First, Fluent API (you should know where to add this code by now):
.Property(p => p.Name)
Now, to do it with an attribute (back over to the
Store model). We’ll just need to add an attribute to the top of
Name property that looks like this:
This can be done with any standard SQL Server type, as long as the property and the column types are compatible. Trying to store a
DateTime in a
bit would just be silly.
Sometimes we’ll need to massage the data a little bit before going into the database. That’s where computed columns come in. Switch over to our
Product model, I’m sure it’s been feeling neglected. Let’s add a property to
PriceWithTax and guess what it’ll store… the price with the tax already calculated! Unfortunately, computed columns are not available when working with attributes, so the Fluent API is only way to go here.
Here’s the modified Product model.
To create the computed column we need to set up a
HasComputedColumnSql function on the
Product table. Like so…
Here I’ve variable-ized the tax rate because I wanted to illustrate that it will be interpreted when the migration is put together. So let’s know create that migration and see how it looks.
If you look at the
ComputedColumnSql property in the
AddColumn block, you’ll see it filled in the tax rate for me. How nice! Now if we run
Update-Database and check out the table and properties.
Here you can see that
PriceWithTax has a new icon and in the General properties group for
PriceWithTax shows our equation and the
IsComputed property is true.
Now whenever a
Product is written to the database, SQL Server will bear the work of computing the value of
PriceWithTax. It isn’t much, but it’s less work for our program to do. Don’t we all want to do a little less work?
There are more transformations included in EF Core, but I’m just trying to get you start with most cases.
That’s the end of what is evidently a week-long trip through EF Core. When I originally thought of writing something on EF Core, I had only planned on a single post. At this point, I hope you feel confident in your ability to implement an EF Core solution.