EF Core HasComputedColumnSql - computed on Insert/Update or each query on SQL Server / AzureSQL?

entity-framework-core sql-server

Question

I'm seeing conflicting information about this. LearnEntityFrameworkCore.com says you can write

.HasComputedColumnSql("GetUtcDate()");

The value of the column is generated by the database's GetUtcDate() method whenever the row is created or updated

However, in the technet documentation for computed columns on SQL Server it says:

Their values are recalculated every time they are referenced in a query.

1
3
5/25/2017 8:54:44 PM

Accepted Answer

Good question. It's unclear from EF Core documentation what type of computed column does HasComputedColumnSql represent. Probably because it's a part of the relational extensions, thus is considered provider specific. But since it has no configuration options, I agree that the behavior should be specified.

A quick test with SQL Server shows that the created column is not physically stored (no PERSISTED option used), and actually it can't since GetUtcDate is not deterministic. Hence the LastModified example from the first link is incorrect - the column will be recalculated any time it's been read, so it cannot be used for the desired intent, and should be created as normal column and updated by the code.

Looks like the intended usage in EF Core is to allow simple calculation based on other columns like FullName or using database specific conversion function with data not stored properly (for instance numeric/date time data stored as text) etc.

1
5/25/2017 5:32:19 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