EF Core Part 6: Transformations

EF Core Part 1: Installation

EF Core Part 2: Dealing with the Database

EF Core Part 3: Working with Database-First

EF Core Part 4: Keys

EF Core Part 5: Relationships

EF Core Part 6: Transformations

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 Add-Migration and Update-Database when we’ve finished with our transformations.

Table Mapping

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 Store to ShoppingExperience.

part6-1

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.

modelBuilder.Entity()
.ToTable("Store");

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.

Column Mapping

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 Store model!

part6-2

We need map our StKey property to a StoreKey column in our Store table. We’ll stick with Fluent API first, so into the DbContext‘s OnModelCreating and we’ll add:

modelBuilder.Entity().
.Property(p => Store.StKey)
.HasColumnName("StoreKey");

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):

modelBuilder.Entity()
.Property(p => p.Name)
.ForSqlServerHasColumnType("nvarchar(100)");

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: [Column(TypeName="nvarchar(100)")].

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.

Computed Columns

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 Product called 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.

part6-3

To create the computed column we need to set up a HasComputedColumnSql function on the Product table. Like so…

part6-4

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.

part6-5

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.

part6-6

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.

Advertisements

6 thoughts on “EF Core Part 6: Transformations

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s