Entity Framework Core: Computed column with persisted values

entity-framework-core sql-server

Question

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.

1
2
10/29/2018 11:30:21 AM

Popular Answer

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.

3
1/28/2019 12:37:35 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow