EF Core – How to add a computed column

by

To add a computed column in EF Core, override DbContext.OnModelCreating() and specify the computed column using ModelBuilder, like this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
	modelBuilder.Entity<Show>()
		.Property(nameof(Show.YearsOnTV))
		.HasComputedColumnSql($"DATEPART(year, GETDATE()) - {nameof(Show.FirstYear)}");
}
Code language: C# (cs)

In this article, I’ll show a full example of adding a computed column and then show how to specify that the computed column should be persisted.

Example of adding a computed column

Let’s say we have a model called Show and we want to add a column called YearsOnTV, which is the number of years since the show first appeared on TV.

First, add the new YearsOnTV property to the Show model:

using System.ComponentModel.DataAnnotations;

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

	[Required]
	[MaxLength(500)]
	public string Name { get; set; }

	[Required]
	[MaxLength(500)]
	public string Description { get; set; }

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

	[Required]
	public int NumberOfSeasons { get; set; }
	
	[Required]
	public int FirstYear { get; set; }
	
	public int? LastYear { get; set; }

	public int YearsOnTV { get; private set; }

	public List<Episode> Episodes { get; set; }

}
Code language: C# (cs)

Note: Since YearsOnTV is a computed column, it’s read-only. Make it have a private setter to indicate this.

Then override DbContext.OnModelCreating(), and use ModelBuilder.HasComputedColumnSql() to define the computed column. In this case, YearsOnTV = CurrentYear – FirstYear:

public class StreamingServiceContext : DbContext
{
	//The rest of StreamingServiceContext not shown
	
	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder.Entity<Show>()
			.Property(nameof(Show.YearsOnTV))
			.HasComputedColumnSql($"DATEPART(year, GETDATE()) - {nameof(Show.FirstYear)}");
	}
}
Code language: C# (cs)

Generate the migration with the dotnet ef tool:

dotnet ef migrations add Database_v14
Code language: PowerShell (powershell)

Take a look at the generated migration source code in <timestamp>_Database_v14.cs:

public partial class Database_v14 : Migration
{
	protected override void Up(MigrationBuilder migrationBuilder)
	{
		migrationBuilder.AddColumn<int>(
			name: "YearsOnTV",
			table: "Shows",
			type: "int",
			nullable: false,
			computedColumnSql: "DATEPART(year, GETDATE()) - FirstYear");
	}

	//Down() not shown
}
Code language: C# (cs)

Now apply the migration programmatically, or with the dotnet ef tool:

dotnet ef database update
Code language: PowerShell (powershell)

Finally, execute a SELECT query with the computed column in the select list:

SELECT [Name], FirstYear,[YearsOnTV]
FROM [dbo].[Shows]
Code language: SQL (Structured Query Language) (sql)

The query returned the following results:

NameFirstYearYearsOnTV
Star Trek: Picard20201

Notice the YearsOnTV is the expected value of 1 (year 2021 – 2020).

Persisting a computed column

When you don’t persist a computed column, then its value is re-computed every time you execute a query. Worse, you may not be able to add the computed column to an index unless it’s persisted (which can lead to poor query performance).

You’ll need to determine if your computed column should be persisted or not. In some cases, it makes sense to re-compute it every time.

The HasComputedColumnSql() method has a stored parameter, but don’t use that. All that does is adds PERSISTED to the column definition. Add PERSISTED yourself. The reason is because then you can add NOT NULL if you need it. EF Core ignores nullable: false for computed columns. In certain scenarios, when you need to make the computed column not null, you’ll have to NOT NULL yourself.

I’ll show an example of how to persist a computed column below.

Example of adding a persisted, non-nullable column

I’ll show an example of how to add a persisted computed column that doesn’t allow nulls. I’ll add a new property called FullName to the Actor model.

First, add the new property to the model:

using System.ComponentModel.DataAnnotations;

public class Actor
{
	[Required]
	[MaxLength(20)]
	public string FirstName { get; set; }

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

	public string FullName { get; private set; }

	[Required]
	public DateTime BirthDate { get; set; }
}
Code language: C# (cs)

Then override DbContext.OnModelCreating() and use the ModelBuilder.HasComputedColumnSql() method to specify the computed column definition:

using Microsoft.EntityFrameworkCore;

public class StreamingServiceContext : DbContext
{
	//The rest of the class is not shown
	
	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder.Entity<Actor>()
			.Property(nameof(Actor.FullName))
			.HasComputedColumnSql($"CONCAT({nameof(Actor.FirstName)},' ', {nameof(Actor.LastName)}) PERSISTED NOT NULL");
	}
}
Code language: C# (cs)

Notice that it has PERSISTED NOT NULL at the end.

Now generate the migration with the dotnet ef tool:

dotnet ef migrations add Database_v15
Code language: PowerShell (powershell)

Take a look at the generated migration source code in <timestamp>_Database_v15.cs:

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

Code language: C# (cs)

Then apply the migration:

dotnet ef database update
Code language: PowerShell (powershell)

Finally, execute a SELECT with the computed column in the select list:

SELECT [FullName]
FROM [dbo].[Actors]
Code language: SQL (Structured Query Language) (sql)

The query returned the following results:

FullName
Michael Douglas
Code language: plaintext (plaintext)

Related Articles