EF Core - Adding a Computed Column

For example:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Employee>()
        .Property(nameof(Employee.Age))
        .HasComputedColumnSql($"DATEDIFF(year, {nameof(Employee.DateOfBirth)}, GETDATE())");
}

This article explains how to add a computed column, including an example and how to specify whether the computed column should be persisted.

Adding a Computed Column

Let’s assume we have an Employee model and we need to add a computed column called Age, which calculates the age of an employee based on their DateOfBirth.

First, add the Age property to the Employee model:

using System.ComponentModel.DataAnnotations;

public class Employee
{
    [Key]
    public int Id { get; set; }

    [Required]
    [MaxLength(100)]
    public string FirstName { get; set; }

    [Required]
    [MaxLength(100)]
    public string LastName { get; set; }

    [Required]
    public DateTime DateOfBirth { get; set; }

    public int Age { get; private set; }
}

Note that Age is a computed column, so it is read-only. It has a private setter to indicate that it cannot be modified directly.

Next, override DbContext.OnModelCreating() and use ModelBuilder.HasComputedColumnSql() to define the computed column. In this case, the computed column will calculate the employee's age as CurrentYear - DateOfBirthYear:

public class CompanyContext : DbContext
{
    // Other parts of the class not shown

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>()
            .Property(nameof(Employee.Age))
            .HasComputedColumnSql($"DATEDIFF(year, {nameof(Employee.DateOfBirth)}, GETDATE())");
    }
}

Generate the migration using the dotnet ef tool:

dotnet ef migrations add AddAgeColumn

Now, look at the generated migration code in <timestamp>_AddAgeColumn.cs:

public partial class AddAgeColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<int>(
            name: "Age",
            table: "Employees",
            type: "int",
            nullable: false,
            computedColumnSql: "DATEDIFF(year, DateOfBirth, GETDATE())");
    }

    // Down() not shown
}

Apply the migration using dotnet ef:

dotnet ef database update

Finally, execute a SELECT query to check the computed column in the results:

SELECT [FirstName], [LastName], [DateOfBirth], [Age]
FROM [dbo].[Employees]

You should get a result similar to this:

This shows that Age is correctly computed based on the employee's DateOfBirth.

Persisting a Computed Column

If you don’t persist a computed column, its value is recalculated each time you query it. However, if you don’t persist the column, it may not be possible to index it, which could affect query performance.

You’ll need to decide whether the computed column should be persisted. In some cases, recalculating the value every time might be fine, but for performance reasons, you may want to persist it.

The HasComputedColumnSql() method allows you to specify whether the column should be persisted. To persist the column, you should manually add the PERSISTED keyword, as EF Core ignores nullable: false for computed columns by default. Additionally, you can specify NOT NULL if needed.

Here’s an example of how to persist a computed column.

Adding a Persisted, Non-Nullable Column

Let’s add a computed, persisted, non-nullable column called FullName to the Employee model, which combines FirstName and LastName.

First, modify the Employee model to include the FullName property:

public class Employee
{
    [Key]
    public int Id { get; set; }

    [Required]
    [MaxLength(100)]
    public string FirstName { get; set; }

    [Required]
    [MaxLength(100)]
    public string LastName { get; set; }

    [Required]
    public DateTime DateOfBirth { get; set; }

    public string FullName { get; private set; }

    public int Age { get; private set; }
}

Override DbContext.OnModelCreating() and use ModelBuilder.HasComputedColumnSql() to define the computed column with the PERSISTED and NOT NULL options:

public class CompanyContext : DbContext
{
    // Other parts of the class not shown

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>()
            .Property(nameof(Employee.FullName))
            .HasComputedColumnSql($"CONCAT(FirstName, ' ', LastName) PERSISTED NOT NULL");
    }
}

Notice the PERSISTED NOT NULL at the end of the column definition.

Now generate a migration using the dotnet ef tool:

dotnet ef migrations add AddFullNameColumn

Check the generated migration code in <timestamp>_AddFullNameColumn.cs:

public partial class AddFullNameColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "FullName",
            table: "Employees",
            type: "nvarchar(max)",
            nullable: false,
            computedColumnSql: "CONCAT(FirstName, ' ', LastName) PERSISTED NOT NULL");
    }

    // Down() not shown
}

Apply the migration:

dotnet ef database update

Finally, execute a SELECT query to retrieve the computed FullName column:

SELECT [FullName]
FROM [dbo].[Employees]

You should see results like this: