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?
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.