Entity Framework Core: Computed column with persisted values

user_2695832 0
Viewed 5289

I'm a little surprised I haven't found any information on the following question, so please excuse if I've missed it somewhere in the docs. Using SQL Server (2016 locally and Azure) and EFCore Code First we're trying to create a computed table column with a persisted value. Creating the column works fine, but I don't have a clue how to persist the value. Here's what we do:

modelBuilder.Entity<SomeClass>(entity =>
{
    entity.Property(p => p.Checksum)
        .HasComputedColumnSql("(checksum([FirstColumnName], [SecondColumnName]))");
});

And here is what we'd actually like to get in T-SQL:

CREATE TABLE [dbo].[SomeClass]
(
    [FirstColumnName]   [NVARCHAR](10)
  , [SecondColumnName]  [NVARCHAR](10)
  , [Checksum] AS (CHECKSUM([FirstColumnName], [SecondColumnName])) PERSISTED
);

Can anyone point me in the right direction?

Thanks in advance, Tobi

UPDATE: Based on a good idea by @jeroen-mostert I also tried to just pass the PERSISTED string as part of the formula:

modelBuilder.Entity<SomeClass>(entity =>
{
    entity.Property(p => p.Checksum)
        .HasComputedColumnSql("(checksum([FirstColumnName], [SecondColumnName]) PERSISTED)");
});

And also outside of the parentheses:

modelBuilder.Entity<SomeClass>(entity =>
{
    entity.Property(p => p.Checksum)
        .HasComputedColumnSql("(checksum([FirstColumnName], [SecondColumnName])) PERSISTED");
});

However und somehow surprisingly, the computed column is still generated with Is Persisted = No, so the PERSISTED string simply seems to be ignored.

entity-framework-core sql-server
3 Answers

Starting with EF Core 5, the HasComputedColumnSql method has a new optional parameter bool? stored to specify that the column should be persisted:

modelBuilder.Entity<SomeClass>()
  .Property(p => p.Checksum)
  .HasComputedColumnSql("checksum([FirstColumnName], [SecondColumnName])", stored: true);
user_26981190

After doing some reading and some tests, I ended up trying the PERSISTED inside the SQL query and it worked.

entity.Property(e => e.Duration_ms)
      .HasComputedColumnSql("DATEDIFF(MILLISECOND, 0, duration) PERSISTED");

The generated migration was the following:

migrationBuilder.AddColumn<long>(
            name: "duration_ms",
            table: "MyTable",
            nullable: true,
            computedColumnSql: "DATEDIFF(MILLISECOND, 0, duration) PERSISTED");

To check on the database whether it is actually persisted I ran the following:

select is_persisted, name from sys.computed_columns where is_persisted = 1

and the column that I've created is there.

user_52682880

" You may also specify that a computed column be stored (sometimes called persisted), meaning that it is computed on every update of the row, and is stored on disk alongside regular columns:"

modelBuilder.Entity<SomeClass>(entity =>
{
     entity.Property(p => p.Checksum)
    .HasComputedColumnSql("(checksum([FirstColumnName], [SecondColumnName]), stored: true);
});

This is taken (and slightly modified) from Microsoft Docs.: https://docs.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=data-annotations#computed-columns

user_125547660
Related