Working of HasComputedColumnSql() method in EF Core

Question

Working of HasComputedColumnSql() method in EF Core

net 6 newbie 121

I have configured the updatedAt property as shown below. I expected that the updatedAt value of the corresponding row alone will change when inserting or updating. But when tried this it is updating entire column i.e all the rows of the UpdatedAt are getting changed which is not expected.

Am I doing any wrong configuration or it is the expected behavior. Please correct the working

modelbuilder

AgaveJoe 30,126 Reputation points

2024-03-09T12:14:31.8366667+00:00

The HasComputedColumnSql() method in EF Core works as expected on my end. Is there anyway you can provide sample code?

net 6 newbie 121

modelbuilder.Entity<Author>()
            .Property(a => a.UpdatedAt)
            .HasComputedColumnSql("GetutcDate()")

AgaveJoe 30,126 Reputation points

2024-03-09T13:25:52.38+00:00
I'm not sure if you understand how HasComputedColumnSql() works. The code executes GetUtcDate() each time the record is fetched.

https://learn.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=data-annotations#computed-columns

Setting the "stored" argument to true persists a computed columns.

modelbuilder.Entity<Author>() .Property(a => a.UpdatedAt) .HasComputedColumnSql("GetutcDate()", stored: true)

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.

I recommend creating a trigger directly in the database using TSQL to update the UpdatedAt. It's a one time script.

CREATE TRIGGER (Transact-SQL)
net 6 newbie 121 Reputation points

2024-03-09T14:18:30.49+00:00

When I have tried the above it is throwing error while doing DB migrations and updating the migrations on DB as follows

Computed column 'UpdatedAt' in table 'Authors' cannot be persisted because the column is non-deterministic.
AgaveJoe 30,126 Reputation points

2024-03-09T16:33:45.4833333+00:00

I created a table using stored: true and it works as documented.

The error is a SQL error related to saving data where the type does not match. It is not clear what steps you performed to get to the position or what the migration looks like.

Perhaps the official documentation will help you figure out what's happening.

Specify Computed Columns in a Table

Again, a trigger is a better option than a computed field for persisting the date a record was updated.
Nahom Asegid 0 Reputation points

2024-03-14T13:25:50.5833333+00:00

in what way do Post to the table where already have computed column ? I tried so many method but i can't do post to the table from EF core.
AgaveJoe 30,126 Reputation points

2024-03-14T14:29:13.0166667+00:00

in what way do Post to the table where already have computed column ? I tried so many method but i can't do post to the table from EF core.

Commuted columns cannot be the target of an insert or update. Guys, please read the official documentation linked in the previous posts.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver16#Limitations
Bruce (SqlWork.com) 77,686 Reputation points Volunteer Moderator

2024-03-14T15:32:17.6866667+00:00

To persist a computed column it must be a deterministic function. That is, every time it’s called with the same arguments it returns the same value. By definition getdate() is not deterministic.

as suggested a trigger is the solution to your requirement.

Nahom Asegid 0

I am beginner developer who tries to encountered an issue while working with Entity Framework Core (EF Core) that I'm hoping to get some insights on.

Scenario:

When attempting to insert data into the Customer table, I encountered the following error:

"The column "UserID" cannot be modified because it is a computed column."

Screenshot 2024-03-16 032150

Objective:

I need a solution to allow the insertion of data into the Customer table while still retaining the functionality of the computed UserID column. I tried so may option like private setter of userID. when I completely remove UserID and ID attribute form The class including the configuration inside the context class it works but i need the userID attribute to be member of the class. Any insights or suggestions would be greatly appreciated!

Settings:

The UserID column is a computed column in SQL Server, generated by a formula specified in EF Core.

The Customer entity is configured in EF Core with the following settings:

 protected override void OnModelCreating(ModelBuilder modelBuilder)
 {
     modelBuilder.Entity<Customer>(entity =>
     {
         entity.HasKey(e => e.UserId);
        
         entity.Property(e => e.UserId)
             .HasMaxLength(7)
             .IsUnicode(false)
             .HasComputedColumnSql("('USER'+right('000'+CONVERT([varchar](3),[ID]),(3)))", true)
             .HasColumnName("UserID")
             .ValueGeneratedOnAdd();
         
         entity.Property(e => e.City).HasMaxLength(100);
         entity.Property(e => e.Company).HasMaxLength(100);
         entity.Property(e => e.Country).HasMaxLength(100);
         entity.Property(e => e.Email).HasMaxLength(100);
         entity.Property(e => e.FirstName).HasMaxLength(100);
         entity.Property(e => e.LocalId).ValueGeneratedOnAdd().HasColumnName("ID");
         entity.Property(e => e.LastName).HasMaxLength(100);
         entity.Property(e => e.MiddleName).HasMaxLength(100);
         entity.Property(e => e.State).HasMaxLength(100);
         entity.Property(e => e.Street1).HasMaxLength(100);
         entity.Property(e => e.Zip).HasMaxLength(20);
     });
     OnModelCreatingPartial(modelBuilder);
 }

AgaveJoe 30,126 Reputation points

2024-03-17T15:00:16.96+00:00

My testing found you need to remove .ValueGeneratedOnAdd(). I created a project that uses a computed column and the computed column is not included in the resulting update script. If ValueGeneratedOnAdd() is present then I get the error message.

It is not clear why you are adding ValueGeneratedOnAdd() as it has been explained several times in this thread, a computed column cannot be the target of an update or insert.

If you continue to have problems after removing ValueGeneratedOnAdd, then there are issues elsewhere in your design we cannot see.

1 answer

Your answer

AgaveJoe 30,126 Reputation points

2024-03-09T12:14:31.8366667+00:00

The HasComputedColumnSql() method in EF Core works as expected on my end. Is there anyway you can provide sample code?
net 6 newbie 121 Reputation points

2024-03-09T12:54:22.3433333+00:00

modelbuilder.Entity<Author>() .Property(a => a.UpdatedAt) .HasComputedColumnSql("GetutcDate()")
AgaveJoe 30,126 Reputation points

2024-03-09T13:25:52.38+00:00

I'm not sure if you understand how HasComputedColumnSql() works. The code executes GetUtcDate() each time the record is fetched.

https://learn.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=data-annotations#computed-columns

Setting the "stored" argument to true persists a computed columns.

modelbuilder.Entity<Author>() .Property(a => a.UpdatedAt) .HasComputedColumnSql("GetutcDate()", stored: true)

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.

I recommend creating a trigger directly in the database using TSQL to update the UpdatedAt. It's a one time script.

CREATE TRIGGER (Transact-SQL)
net 6 newbie 121 Reputation points

2024-03-09T14:18:30.49+00:00

When I have tried the above it is throwing error while doing DB migrations and updating the migrations on DB as follows

Computed column 'UpdatedAt' in table 'Authors' cannot be persisted because the column is non-deterministic.
AgaveJoe 30,126 Reputation points

2024-03-09T16:33:45.4833333+00:00

I created a table using stored: true and it works as documented.

The error is a SQL error related to saving data where the type does not match. It is not clear what steps you performed to get to the position or what the migration looks like.

Perhaps the official documentation will help you figure out what's happening.

Specify Computed Columns in a Table

Again, a trigger is a better option than a computed field for persisting the date a record was updated.
Nahom Asegid 0 Reputation points

2024-03-14T13:25:50.5833333+00:00

in what way do Post to the table where already have computed column ? I tried so many method but i can't do post to the table from EF core.
AgaveJoe 30,126 Reputation points

2024-03-14T14:29:13.0166667+00:00

in what way do Post to the table where already have computed column ? I tried so many method but i can't do post to the table from EF core.

Commuted columns cannot be the target of an insert or update. Guys, please read the official documentation linked in the previous posts.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver16#Limitations
Bruce (SqlWork.com) 77,686 Reputation points Volunteer Moderator

2024-03-14T15:32:17.6866667+00:00

To persist a computed column it must be a deterministic function. That is, every time it’s called with the same arguments it returns the same value. By definition getdate() is not deterministic.

as suggested a trigger is the solution to your requirement.
Nahom Asegid 0 Reputation points

2024-03-17T07:07:08.19+00:00

I am beginner developer who tries to encountered an issue while working with Entity Framework Core (EF Core) that I'm hoping to get some insights on.

Scenario:

When attempting to insert data into the Customer table, I encountered the following error:

"The column "UserID" cannot be modified because it is a computed column."

Objective:

I need a solution to allow the insertion of data into the Customer table while still retaining the functionality of the computed UserID column. I tried so may option like private setter of userID. when I completely remove UserID and ID attribute form The class including the configuration inside the context class it works but i need the userID attribute to be member of the class. Any insights or suggestions would be greatly appreciated!

Settings:

The UserID column is a computed column in SQL Server, generated by a formula specified in EF Core.

The Customer entity is configured in EF Core with the following settings:

protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Customer>(entity => { entity.HasKey(e => e.UserId); entity.Property(e => e.UserId) .HasMaxLength(7) .IsUnicode(false) .HasComputedColumnSql("('USER'+right('000'+CONVERT([varchar](3),[ID]),(3)))", true) .HasColumnName("UserID") .ValueGeneratedOnAdd(); entity.Property(e => e.City).HasMaxLength(100); entity.Property(e => e.Company).HasMaxLength(100); entity.Property(e => e.Country).HasMaxLength(100); entity.Property(e => e.Email).HasMaxLength(100); entity.Property(e => e.FirstName).HasMaxLength(100); entity.Property(e => e.LocalId).ValueGeneratedOnAdd().HasColumnName("ID"); entity.Property(e => e.LastName).HasMaxLength(100); entity.Property(e => e.MiddleName).HasMaxLength(100); entity.Property(e => e.State).HasMaxLength(100); entity.Property(e => e.Street1).HasMaxLength(100); entity.Property(e => e.Zip).HasMaxLength(20); }); OnModelCreatingPartial(modelBuilder); }
AgaveJoe 30,126 Reputation points

2024-03-17T15:00:16.96+00:00

My testing found you need to remove .ValueGeneratedOnAdd(). I created a project that uses a computed column and the computed column is not included in the resulting update script. If ValueGeneratedOnAdd() is present then I get the error message.

It is not clear why you are adding ValueGeneratedOnAdd() as it has been explained several times in this thread, a computed column cannot be the target of an update or insert.

If you continue to have problems after removing ValueGeneratedOnAdd, then there are issues elsewhere in your design we cannot see.

Answer 1

See my article with source code

https://dev.to/karenpayneoregon/sql-server-computed-columns-with-ef-core-3h8d

Example from article

internal class Context : DbContext
{
    public DbSet<Person> Persons { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(ConnectionString())
            .EnableSensitiveDataLogging()
            .LogTo(message => Debug.WriteLine(message), LogLevel.Information)
            .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>()
            .Property(p => p.DisplayName)
            .HasComputedColumnSql("[Title] + ' ' + [FirstName] + ' ' + [LastName]");

        modelBuilder.Entity<Person>().HasData(
            new Person() { Id = 1, FirstName = "Karen", LastName = "Payne", Title = "Miss" },
            new Person() { Id = 2, FirstName = "Jane", LastName = "Adams", Title = "Miss" }
        );

    }
}

Share via

Working of HasComputedColumnSql() method in EF Core

1 answer

Your answer