How to Create Computed Columns in Entity Framework Core

I recently ran into an challenge when using Entity Framework Core. I wanted to search across a property which consisted of an integer and a prefix. The example at the end of this post goes through the solution.

I'll start off with a common scenario.

Say for example you want to search for a user across their Full name. However,your table only contains their Firstname and Lastname.

public class StoreUser : IdentityUser
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }

    }

Your search input may look something like this, it takes a string value and finds users based on that input string.

The current table design restricts the ability to search across the users' Full name. I could resort to writing something like this in the repository:

        {
            return context.Users.Where(u => u.FirstName.ToLower().Contains(searchTerm) 
            || u.LastName.ToLower().Contains(searchTerm));
        }

Using this approach, searching using the term 'Eamon K' in the search input would yield no results.

Entity Framework would generate TSQL that would search the table and check if FirstName had the term 'Eamon K'. False.
It would then do the same for LastName, also return false. Thus it would return no results.

This isn't the behaviour we've come to expect from a search by term input shown above. Here's how I went about fixing it.

Introducing Entity Framework computed columns.

We could simply introduce a new property to the StoreUser class called Fullname. Then introduce some code on user creation or update that checks the FirstName and LastName and updates the Fullname property.

public class StoreUser : IdentityUser
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public string FullName { get; set; }

    }

There's a better way though. We'd rather not have to deal with maintaining this Fullname property.

First we'll set the FullName property to have a private setter to avoid unintentionally assigning it.

public class StoreUser : IdentityUser
   {
       public string FirstName { get; set; }
       public string LastName { get; set; }
       public string FullName { get; private set; }

   }

Then we'll use the fluent API to define how we want this column to be calculated in the database. We're doing this withing the Within the OnModelCreating method.

protected override void OnModelCreating(ModelBuilder modelBuilder)
       {
           base.OnModelCreating(modelBuilder);

           modelBuilder.Entity<StoreUser>()
               .Property(u => u.FullName)
               .HasComputedColumnSql("[FirstName] + ' ' + [LastName]");
       }

Once that has been added, add new migration. Mine looked like this:

public partial class computedcolumnadded : Migration
  {
      protected override void Up(MigrationBuilder migrationBuilder)
      {
          migrationBuilder.AddColumn<string>(
              name: "FullName",
              table: "AspNetUsers",
              nullable: true,
              computedColumnSql: "[FirstName] + ' ' + [LastName]");
      }

      protected override void Down(MigrationBuilder migrationBuilder)
      {
          migrationBuilder.DropColumn(
              name: "FullName",
              table: "AspNetUsers");
      }
  }

Update the database and you should now see the new column added to the table!

Now back to why we made this change in the first place ....

We can rewrite our repository method like so

  public IQueryable<StoreUser> GetUsersByTerm(string searchTerm)
        {
            return context.Users.Where(u => u.FullName.ToLower().Contains(searchTerm));
        }

Making this change allows us to search using the term 'Eamon K' and return the correct user. You could add an index to this column if needed.

Computed columns are very powerful. Entity Framework Core with its fluent API allows them to be easily added.

You'll often see scenarios where a property is made up for a underlying incrementing number along with a prefix or suffix.

This is a perfect place to take advantage of computed columns.

Here's another quick example:

You're a company that conducts tests. The test has a primary key of type int and also has a property named 'TestReference'.

Test reference is made up of the primary key along with a prefix

You could use Entity Framework Fluent API to configure this as

modelBuilder.Entity<Test>()
           .Property(t => t.TestReference)
           .HasComputedColumnSql("N'Test'+ RIGHT('00000'+CAST(Id AS VARCHAR(5)),5)");

This allows you to now query the database for this TestReference column without having to worry about maintaining it.

More information on computed columns can be found in the Microsoft documentation here

I hope this post helped somebody out. Let me know if you have any questions. You can find me on twitter @eamokeane. Say hello!