How to Create Computed Columns in Entity Framework Core
Tags: entityframworkcore, csharp, sqlserver
Published: 2021-01-31 16:56:04

---
title: How to Create Computed Columns in Entity Framework Core
published: true
date: 2020-05-03 10:18:36 UTC
tags: EntityFramworkCore,CSharp,SQLServer
canonical_url: https://eamonkeane.dev/computed-columns-in-entity-framework-core/
---

 ![How to Create Computed Columns in Entity Framework Core](https://eamonkeane.dev/content/images/2020/05/EF-Core-computed-columns-2.png)

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_.

![How to Create Computed Columns in Entity Framework Core](https://eamonkeane.dev/content/images/2020/05/1.png)<figcaption>Typical table design for a user. It stores their Firstname and Lastname.</figcaption>

<!--kg-card-begin: markdown-->
```
public class StoreUser : IdentityUser
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }

    }

```
<!--kg-card-end: markdown-->

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

![How to Create Computed Columns in Entity Framework Core](https://eamonkeane.dev/content/images/2020/05/image-5.png)<figcaption>Search input to find Users</figcaption>

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:

<!--kg-card-begin: markdown-->
```
        {
            return context.Users.Where(u => u.FirstName.ToLower().Contains(searchTerm) 
            || u.LastName.ToLower().Contains(searchTerm));
        }
```
<!--kg-card-end: markdown-->

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

![How to Create Computed Columns in Entity Framework Core](https://eamonkeane.dev/content/images/2020/05/image-6.png)<figcaption>User in the database. We only store the FirstName and LastName. (Viewed using SQL Server Management Studio).</figcaption>

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.

<!--kg-card-begin: markdown-->
```
public class StoreUser : IdentityUser
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public string FullName { get; set; }

    }
```
<!--kg-card-end: markdown-->

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.

<!--kg-card-begin: markdown-->
```
public class StoreUser : IdentityUser
   {
       public string FirstName { get; set; }
       public string LastName { get; set; }
       public string FullName { get; private set; }

   }
```
<!--kg-card-end: markdown-->

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.

<!--kg-card-begin: markdown-->
```
protected override void OnModelCreating(ModelBuilder modelBuilder)
       {
           base.OnModelCreating(modelBuilder);

           modelBuilder.Entity<StoreUser>()
               .Property(u => u.FullName)
               .HasComputedColumnSql("[FirstName] + ' ' + [LastName]");
       }
```
<!--kg-card-end: markdown-->

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

<!--kg-card-begin: markdown-->
```
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");
      }
  }
```
<!--kg-card-end: markdown-->

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

![How to Create Computed Columns in Entity Framework Core](https://eamonkeane.dev/content/images/2020/05/image-9.png)<figcaption>SQL server understands that this column is computed. </figcaption>

![How to Create Computed Columns in Entity Framework Core](https://eamonkeane.dev/content/images/2020/05/image-10.png)<figcaption>Computed column specification added. (Viewed using SQL Server Management Studio).</figcaption>

![How to Create Computed Columns in Entity Framework Core](https://eamonkeane.dev/content/images/2020/05/image-7.png)<figcaption>SQL computed column added</figcaption>

Now back to why we made this change in the first place ....  
  
We can rewrite our repository method like so

<!--kg-card-begin: markdown-->
```
  public IQueryable<StoreUser> GetUsersByTerm(string searchTerm)
        {
            return context.Users.Where(u => u.FullName.ToLower().Contains(searchTerm));
        }
```
<!--kg-card-end: markdown-->

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

![How to Create Computed Columns in Entity Framework Core](https://eamonkeane.dev/content/images/2020/05/image-8.png)<figcaption>Example of how the TestReference column might look in the table.</figcaption>

You could use Entity Framework Fluent API to configure this as

<!--kg-card-begin: markdown-->
```
modelBuilder.Entity<Test>()
           .Property(t => t.TestReference)
           .HasComputedColumnSql("N'Test'+ RIGHT('00000'+CAST(Id AS VARCHAR(5)),5)");
```
<!--kg-card-end: markdown-->

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

[

Generated Values - EF Core

How to configure value generation for properties when using Entity Framework Core

 ![How to Create Computed Columns in Entity Framework Core](https://docs.microsoft.com/favicon.ico)AndriySvyrydMicrosoft Docs

 ![How to Create Computed Columns in Entity Framework Core](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)
](https://docs.microsoft.com/en-us/ef/core/modeling/generated-properties)

I hope this post helped somebody out. Let me know if you have any questions. You can find me on twitter [@ **eamokeane**](https://twitter.com/eamokeane). Say hello!