How to add a computed column in EF Core

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .Property(nameof(Order.TotalAmount))
        .HasComputedColumnSql($"Price * Quantity");
}

In this guide, I’ll walk through an example of adding a computed column, and how to configure it to be persisted.

Adding a Computed Column

Let’s assume we have a model called Order, and we want to add a computed column called TotalAmount, which is the result of multiplying the Price by the Quantity.

First, we add the new TotalAmount property to the Order model:

public class Order
{
    [Key]
    public int OrderId { get; set; }

    [Required]
    public decimal Price { get; set; }

    [Required]
    public int Quantity { get; set; }

    public decimal TotalAmount { get; private set; } // Computed column

    public DateTime OrderDate { get; set; }
}

Since TotalAmount is a computed column, it’s read-only, so we’ve made the setter private.

Next, override the DbContext.OnModelCreating() method and use ModelBuilder.HasComputedColumnSql() to define the computed column. In this case, TotalAmount is calculated as Price * Quantity:

public class OrderDbContext : DbContext
{
    // Other DbSets not shown for brevity

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>()
            .Property(nameof(Order.TotalAmount))
            .HasComputedColumnSql("Price * Quantity");
    }
}

Then, generate the migration using the dotnet ef tool:

dotnet ef migrations add AddTotalAmountColumn

Review the generated migration code, typically found in <timestamp>_AddTotalAmountColumn.cs:

public partial class AddTotalAmountColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<decimal>(
            name: "TotalAmount",
            table: "Orders",
            type: "decimal(18,2)",
            nullable: false,
            computedColumnSql: "Price * Quantity");
    }

    // Down() method not shown
}

Finally, apply the migration:

dotnet ef database update

After the migration, you can query the computed column:

SELECT [OrderId], [Price], [Quantity], [TotalAmount] 
FROM [dbo].[Orders]

The query will return the following results:

Persisting a Computed Column

When you don't persist a computed column, its value is re-computed every time you query the database, which can impact performance. In some cases, you might want to persist the computed value so that it is stored in the database and not recalculated each time.

By default, EF Core doesn’t persist computed columns unless specified. To persist a computed column, you'll need to manually add PERSISTED to the column definition.

The HasComputedColumnSql() method has an optional parameter to specify PERSISTED, but you must add it manually to achieve finer control, such as enforcing the column to be non-nullable.

Adding a Persisted, Non-Nullable Column

Let’s say you want to add a computed column FullName to the Customer model. This column will concatenate the FirstName and LastName properties, and it should be persisted and non-nullable.

First, add the FullName property to the Customer model:

public class Customer
{
    [Key]
    public int CustomerId { get; set; }

    [Required]
    public string FirstName { get; set; }

    [Required]
    public string LastName { get; set; }

    public string FullName { get; private set; } // Computed column

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

Then, in DbContext.OnModelCreating(), configure the computed column to be persisted and non-nullable:

public class CustomerDbContext : DbContext
{
    // Other DbSets not shown for brevity

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

Now, generate the migration using the dotnet ef tool:

dotnet ef migrations add AddFullNameColumn

Here’s what the generated migration might look like in <timestamp>_AddFullNameColumn.cs:

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

    // Down() method not shown
}

Apply the migration:

dotnet ef database update

Finally, you can query the FullName column:

SELECT [FullName] 
FROM [dbo].[Customers]