How to set default values for shadow property

c# entity-framework-core

Question

I have the following entity:

public class Person
{
    public Guid Id { get; set; }

    public string Name { get; set; }
}

This is my DB context

public class PersonDbContext : DbContext
{
    private static readonly ILoggerFactory
        Logger = LoggerFactory.Create(x => x.AddConsole());

    public DbSet<Person> Persons { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseLoggerFactory(Logger)
            .UseSqlServer(
                "Server=(localdb)\\mssqllocaldb;Database=PersonDb;Trusted_Connection=True;MultipleActiveResultSets=true");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<Person>()
            .Property<DateTime>("Created")
            .HasDefaultValueSql("GETUTCDATE()")
            .ValueGeneratedOnAdd();

        modelBuilder
            .Entity<Person>()
            .Property<DateTime>("Updated")
            .HasDefaultValueSql("GETUTCDATE()")
            .ValueGeneratedOnAddOrUpdate();
    }
}

As can be seen from OnModelCreating override, I'm adding shadow properties Updated/Created to Person entity.

I set those properties to be populated with SQL default values

  • Created when value is added
  • Updated when value is added or updated

Below is client code

var personId = Guid.Parse("CF5EE27D-C694-408A-9F7B-080FF6315843");

using (var dbContext = new PersonDbContext())
{
    var person = new Person
    {
        Id = personId,
        Name = "New Person"
    };

    dbContext.Add(person);

    await dbContext.SaveChangesAsync();
}

using (var dbContext = new PersonDbContext())
{
    var person = dbContext.Persons.Find(personId);

    var personName = person.Name;

    person.Name = $"{personName} {DateTime.UtcNow}";

    dbContext.SaveChanges();
}

I can confirm that both properties are set to UTC date when inserting a new person. However, on update, Updated property is not being set.

This is the t-sql generated:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@p1='?' (DbType = Guid), @p0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      UPDATE [Persons] SET [Name] = @p0
      WHERE [Id] = @p1;
      SELECT [Updated]
      FROM [Persons]
      WHERE @@ROWCOUNT = 1 AND [Id] = @p1;

Reading documentation on genereted value on add or update I see the following warning:

However, if you specify that a DateTime property is generated on add or update, then you must setup a way for the values to be generated. One way to do this, is to configure a default value of GETDATE() (see Default Values) to generate values for new rows. You could then use a database trigger to generate values during updates (such as the following example trigger).

I don't get what's the purpose of ValueGeneratedOnAddOrUpdate() then, if it behaves like ValueGeneratedOnAdd() and I have to manually intervene (creating a trigger) to set this property.

Indeed, if I change the definition of Updated shadow property to

modelBuilder
    .Entity<Person>()
    .Property<DateTime>("Updated")
    .HasDefaultValueSql("GETUTCDATE()")
    .ValueGeneratedOnAdd();

And override SaveChanges on the PersonDbContext

public override int SaveChanges()
{
    ChangeTracker.DetectChanges();

    foreach (var entry in ChangeTracker.Entries().Where(entity => entity.State == EntityState.Modified))
    {
        entry.Property("Updated").CurrentValue = DateTime.UtcNow;
    }

    return base.SaveChanges();
}

This does what is expected.

So the question is - what's the proper way to set default values for shadow properties in EF Core.

This is simplified example from my bigger project, so using HasData on the entities in OnModelCreating override is not a good option (due to many entities).

I'm using EF Core 3.1.1

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.1.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.1" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.1"/>
1
2
2/14/2020 2:14:24 PM

Accepted Answer

I found dicussion where it is pointed that name ValueGeneratedOnAddOrUpdate() is not descriptive enough.

@rowanmiller Thanks for the quick reply! Now I know how it works, but I think it's a bit confusing. The naming "ValueGeneratedOnAddOrUpdate" suggests that a value actually is generated on insert and update.

And the docs says: "Value generated on add" "Value generated on add means that if you don’t specify a value, one will be generated for you."

"Value generated on add or update" "Value generated on add or update means that a new value is generated every time the record is saved (insert or update)."

Maybe add a section in the docs that describes how you supply your own value generator?

However, advice from EF team is to follow the warning section and apply manual steps to generate values for Update case manually.

@bcbeatty the detailed explanation is included in the main section, and there is a caution box in the Fluent and Data Annotation sections that points back to the detailed note.

This just lets EF know that values are generated for added entities, it does not guarantee that EF will setup the actual mechanism to generate values. See Value generated on add section for more details.

So it seems, that at least for DateTime shadow properties, approach in OP is the way to go.

0
2/17/2020 10:44:21 AM

Popular Answer

if you want to have reusable shadow properties follow these steps.

1- create a marker empty interface. IAuditableEntity.cs

    /// <summary>
    /// It's a marker interface, in order to make our entities audit-able.
    /// Every entity you mark with this interface, will save audit info to the database.
    /// </summary>
    public interface IAuditableEntity
    { }

2- Create a static Class to write your Shadow Properties logic. AuditableShadowProperties.cs

public static class AuditableShadowProperties {

    public static readonly Func<object, DateTimeOffset?> EfPropertyCreatedDateTime =
        entity => EF.Property<DateTimeOffset?> (entity, CreatedDateTime);

    public static readonly string CreatedDateTime = nameof (CreatedDateTime);

    public static readonly Func<object, DateTimeOffset?> EfPropertyModifiedDateTime =
        entity => EF.Property<DateTimeOffset?> (entity, ModifiedDateTime);

    public static readonly string ModifiedDateTime = nameof (ModifiedDateTime);

    public static void AddAuditableShadowProperties (this ModelBuilder modelBuilder) {
        foreach (var entityType in modelBuilder.Model
                .GetEntityTypes ()
                .Where (e => typeof (IAuditableEntity).IsAssignableFrom (e.ClrType))) {
            modelBuilder.Entity (entityType.ClrType)
                .Property<DateTimeOffset?> (CreatedDateTime);

            modelBuilder.Entity (entityType.ClrType)
                .Property<DateTimeOffset?> (ModifiedDateTime);

        }
    }

    public static void SetAuditableEntityPropertyValues (
        this ChangeTracker changeTracker) {
        var now = DateTimeOffset.UtcNow;

        var modifiedEntries = changeTracker.Entries<IAuditableEntity> ()
            .Where (x => x.State == EntityState.Modified);
        foreach (var modifiedEntry in modifiedEntries) {
            modifiedEntry.Property (ModifiedDateTime).CurrentValue = now;
        }

        var addedEntries = changeTracker.Entries<IAuditableEntity> ()
            .Where (x => x.State == EntityState.Added);
        foreach (var addedEntry in addedEntries) {
            addedEntry.Property (CreatedDateTime).CurrentValue = now;
        }
    }
}

3- Add neccessery changes to your PersonDbContext to use your IAuditableEntity.

 // first we add our shadow properties to the database with next migration
 protected override void OnModelCreating(ModelBuilder builder)
{
...
  builder.AddAuditableShadowProperties();
}

// override saveChanges methods to use our shadow properties.
        public override int SaveChanges()
        {
            ChangeTracker.DetectChanges();

            BeforeSaveTriggers();

            ChangeTracker.AutoDetectChangesEnabled =
                false; // for performance reasons, to avoid calling DetectChanges() again.
            var result = base.SaveChanges();
            ChangeTracker.AutoDetectChangesEnabled = true;
            return result;
        }

     public override Task<int> SaveChangesAsync(CancellationToken cancellationToken = new CancellationToken())
        {
            ChangeTracker.DetectChanges();

            BeforeSaveTriggers();

            ChangeTracker.AutoDetectChangesEnabled =
                false; // for performance reasons, to avoid calling DetectChanges() again.
            var result = base.SaveChangesAsync(cancellationToken);
            ChangeTracker.AutoDetectChangesEnabled = true;
            return result;
        }

        public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess,
            CancellationToken cancellationToken = new CancellationToken())
        {
            ChangeTracker.DetectChanges();

            BeforeSaveTriggers();

            ChangeTracker.AutoDetectChangesEnabled =
                false; // for performance reasons, to avoid calling DetectChanges() again.
            var result = base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
            ChangeTracker.AutoDetectChangesEnabled = true;
            return result;
        }

        #region "ExtraMethods"

        public T GetShadowPropertyValue<T>(object entity, string propertyName) where T : IConvertible
        {
            var value = this.Entry(entity).Property(propertyName).CurrentValue;
            return value != null ?
                (T)Convert.ChangeType(value, typeof(T), CultureInfo.InvariantCulture) :
                default(T);
        }

        public object GetShadowPropertyValue(object entity, string propertyName)
        {
            return this.Entry(entity).Property(propertyName).CurrentValue;
        }


        private void BeforeSaveTriggers()
        {
            ValidateEntities();
            SetShadowProperties();
        }

        private void ValidateEntities()
        {
            var errors = this.GetValidationErrors();
            if (!string.IsNullOrWhiteSpace(errors))
            {
                // we can't use constructor injection anymore, because we are using the `AddDbContextPool<>`
                var loggerFactory = this.GetService<ILoggerFactory>();
                loggerFactory.CheckArgumentIsNull(nameof(loggerFactory));
                var logger = loggerFactory.CreateLogger<AppDbContext>();
                logger.LogError(errors);
                throw new InvalidOperationException(errors);
            }
        }

        private void SetShadowProperties()
        {
            ChangeTracker.SetAuditableEntityPropertyValues();
        }
        #endregio

usage:

4- now you can add IAuditableEntity interface to any entity you want to have these shadow properties and you are done.

public class Person : IAuditableEntity
{
    public Guid Id { get; set; }

    public string Name { get; set; }
}

I'm using IAuditableEntity with a lot of other properties such as BrowserName userIp ... but I removed those in this example to keep it as simple as possible. it's not easy to explain everything in this example but feel free to ask if you had any questions about this approach.



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