DEV Community

loading...

How to Create Computed Columns in Entity Framework Core

galwaycoder profile image Eamon Keane Originally published at eamonkeane.dev on ・4 min read

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.

How to Create Computed Columns in Entity Framework Core
Typical table design for a user. It stores their Firstname and Lastname.

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

    }

Enter fullscreen mode Exit fullscreen mode

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
Search input to find Users

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));
        }
Enter fullscreen mode Exit fullscreen mode

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
User in the database. We only store the FirstName and LastName. (Viewed using SQL Server Management Studio).

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; }

    }
Enter fullscreen mode Exit fullscreen mode

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; }

   }
Enter fullscreen mode Exit fullscreen mode

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]");
       }
Enter fullscreen mode Exit fullscreen mode

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");
      }
  }
Enter fullscreen mode Exit fullscreen mode

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

How to Create Computed Columns in Entity Framework Core
SQL server understands that this column is computed.

How to Create Computed Columns in Entity Framework Core
Computed column specification added. (Viewed using SQL Server Management Studio).

How to Create Computed Columns in Entity Framework Core
SQL computed column added

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));
        }
Enter fullscreen mode Exit fullscreen mode

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
Example of how the TestReference column might look in the table.

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)");
Enter fullscreen mode Exit fullscreen mode

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 CoreAndriySvyrydMicrosoft Docs

How to Create Computed Columns in Entity Framework Core
](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. Say hello!

Discussion (0)

pic
Editor guide