Computed primary key doesn't get updated in EF Core entity after insert

.net-core c# ef-core-2.1 entity-framework-core

Question

I have a special case where the Id of the table is defined as a computed column like this:

CREATE TABLE [BusinessArea](
    [Id]  AS (isnull((CONVERT([nvarchar],[CasaId],(0))+'-')+CONVERT([nvarchar],[ConfigurationId],(0)),'-')) PERSISTED NOT NULL,
    [CasaId] [int] NOT NULL,
    [ConfigurationId] [int] NOT NULL,
    [Code] [nvarchar](4) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_BusinessArea] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO

Usually when I have a computed column i configure it like this:

builder.Entity<MyEntity>()
 .Property(p => p.MyComputed).HasComputedColumnSql(null);

With .HasComputedColumnSql() the value of MyComputed is reflected an insert/update on the entity.

However this trick doesn't work if the computed column is a PK.

Any idea on how to make that work also with a PK?

1
2
10/22/2018 11:25:56 AM

Accepted Answer

It can be made to work, but only for insert, by setting property BeforeSaveBehavior to Ignore:

modelBuilder.Entity<BusinessArea>().Property(e => e.Id)
    .Metadata.BeforeSaveBehavior = PropertySaveBehavior.Ignore;

But in general such design will cause problems with EF Core because it doesn't support mutable keys (primary or alternate). Which means that it would never retrieve back the Id from database after update. You can verify that by marking the property as ValueGeneratedOnAddOrUpdate (which is the normal behavior for computed columns):

modelBuilder.Entity<BusinessArea>().Property(e => e.Id)
    .ValueGeneratedOnAddOrUpdate();

If you do so, EF Core will throw InvalidOperationException saying

The property 'Id' cannot be configured as 'ValueGeneratedOnUpdate' or 'ValueGeneratedOnAddOrUpdate' because the key value cannot be changed after the entity has been added to the store.

2
10/23/2018 7:25:50 AM


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