How to set created date and Modified Date to enitites in DB first approach

Question

In every SQL table in our application we have "CreatedDate" and "ModifiedDate" column. We are using DB first approach. When i save the data, i want these two column automatically populated. One approach is to have Default Value as getdate() on the SQL Table Column itself. So thats going to solve the problem partially. Meaning it will set the CreatedDate & ModifiedDate when entity is new. However when i am editing/updating the entity i want only ModifiedDate to be updated.
There are lot of articles doing it using Code first approach. But we are using DB first approach.
What are my options here?

Highchair

Override SaveChanges in your context. jigar.net/articles/viewhtmlcontent344.aspx – Harriettharrietta

If you already use default values from the database then it should also be the database that modifies ModifiedDate by a trigger. It's never guaranteed that client clocks match. That said, I'd use triggers for this always. – Ridgway

Answer 1

See this answer for .NET Core:

https://mcmap.net/q/138811/-how-to-get-user-information-in-dbcontext-using-net-core

I like a more generic approach used like this:

interface IEntityDate
{
    DateTime CreatedDate { get; set; }

    DateTime UpdatedDate { get; set; }
}

public abstract class EntityBase<T1>: IEntityDate
{
    public T1 Id { get; set; }

    public virtual DateTime CreatedDate { get; set; }
    public virtual string CreatedBy { get; set; }
    public virtual DateTime UpdatedDate { get; set; }
    public virtual string UpdatedBy { get; set; }
}

public override int SaveChanges()
{
    var now = DateTime.UtcNow;

    foreach (var changedEntity in ChangeTracker.Entries())
    {
        if (changedEntity.Entity is IEntityDate entity)
        {
            switch (changedEntity.State)
            {
                case EntityState.Added:
                    entity.CreatedDate = now;
                    entity.UpdatedDate = now;
                    break;

                case EntityState.Modified:
                    Entry(entity).Property(x => x.CreatedDate).IsModified = false;
                    entity.UpdatedDate = now;
                    break;
            }
        }
    }

    return base.SaveChanges();
}

Update:

To handle CreatedBy and UpdatedBy I use a wrapper for DbContext like this:

public interface IEntity
{
    DateTime CreatedDate { get; set; }

    string CreatedBy { get; set; }

    DateTime UpdatedDate { get; set; }

    string UpdatedBy { get; set; }
}

public interface ICurrentUser
{
    string GetUsername();
}

public class ApplicationDbContextUserWrapper
{
    public ApplicationDbContext Context;

    public ApplicationDbContextUserWrapper(ApplicationDbContext context, ICurrentUser currentUser)
    {
        context.CurrentUser = currentUser;
        this.Context = context;
    }
}

public class ApplicationDbContext : DbContext
{

    public ICurrentUser CurrentUser;
    
    public override int SaveChanges()
    {
        var now = DateTime.UtcNow;

        foreach (var changedEntity in ChangeTracker.Entries())
        {
            if (changedEntity.Entity is IEntity entity)
            {
                switch (changedEntity.State)
                {
                    case EntityState.Added:
                        entity.CreatedDate = now;
                        entity.UpdatedDate = now;
                        entity.CreatedBy = CurrentUser.GetUsername();
                        entity.UpdatedBy = CurrentUser.GetUsername();
                        break;
                    case EntityState.Modified:
                        Entry(entity).Property(x => x.CreatedBy).IsModified = false;
                        Entry(entity).Property(x => x.CreatedDate).IsModified = false;
                        entity.UpdatedDate = now;
                        entity.UpdatedBy = CurrentUser.GetUsername();
                        break;
                }
            }
        }

        return base.SaveChanges();
    }
    
    ...

Strontium

how are you setting, UpdatedBy? – Periphrastic

@IvanSalo Updated my answer with how I solved this. – Strontium

how about i want to override the autocreation and want to set it manually in the controllers – Mcintire

Wow this worked great for me!! Thanks a lot @Ogglas!! – Geist

The question was for Database First approach. This will not work for Database First as the context file gets generated. – Sulfurous

@Sulfurous It is possible to create the specialized class (ApplicationDbContext) as demonstrated in the update, or to put the override in a partial class file. – Eadith

Answer 2

For those who are using asynchronous system (SaveChangesAsync) and .net core better to add below code to DbContext class.

    public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken))
    {
        var AddedEntities = ChangeTracker.Entries().Where(E => E.State == EntityState.Added).ToList();

        AddedEntities.ForEach(E =>
        {
            E.Property("CreationTime").CurrentValue = DateTime.Now;
        });

        var EditedEntities = ChangeTracker.Entries().Where(E => E.State == EntityState.Modified).ToList();

        EditedEntities.ForEach(E =>
        {
            E.Property("ModifiedDate").CurrentValue = DateTime.Now;
        });

        return base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
    }

and also you can define a class or interface like below.

public class SaveConfig
{
    public DateTime CreationTime { get; set; }
    public DateTime? ModifiedDate { get; set; }
}

just inherit entities from the SaveConfig entity.

Monge

Perfect, thanks! Also, if you do inherit from the SaveConfig, you can use ChangeTracker.Entries<SaveConfig>()... and then replace E.Property("CreationTime").CurrentValue with E.Entity.CreationTime, avoiding magic strings :) – Vomiturition

This worked like a charm. I used your code to override SaveChangesAsync() and this code to override SaveChanges(). – Burgee

Answer 3

If you'd like to override OnSave you have to override all save methods.In EF core 2.1 you can use better solution with ChangeTracked and events. For example:

You can create interface or base class like example below:

public interface IUpdateable 
{ 
    DateTime ModificationDate{ get; set; }
}

public class SampleEntry : IUpdateable
{
    public int Id { get; set; }
    public DateTime ModificationDate { get; set; }
} 

Then on context creation add event to Change tracker:

context.ChangeTracker.StateChanged += context.ChangeTracker_StateChanged;

And method:

private void ChangeTracker_StateChanged(object sender, Microsoft.EntityFrameworkCore.ChangeTracking.EntityStateChangedEventArgs e)
    {
        if(e.Entry.Entity is IUpdateable && e.Entry.State == EntityState.Modified)
        {
            var entry = ((IUpdateable)e.Entry.Entity);
            entry.ModificationDate = DateTime.Now;
        }
    }

It's easier and you don't have to override all methods.

Furcula

See this post to read a related thread and have a more detailed solution #53187646 – Boxing

Answer 4

You can override SaveChanges method in DbContext and get a list of Added and Modified your entity and if it is added set CreatedDate and if it is modified set ModifiedDate.

public override int SaveChanges()
{
    var AddedEntities = ChangeTracker.Entries<Entity>().Where(E => E.State == EntityState.Added).ToList();

    AddedEntities.ForEach(E => 
    {
        E.CreatedDate = DateTime.Now;
    });

    var ModifiedEntities = ChangeTracker.Entries<Entity>().Where(E => E.State == EntityState.Modified).ToList();

    ModifiedEntities.ForEach(E => 
    {
        E.ModifiedDate = DateTime.Now;
    });

    return base.SaveChanges();
}

You can also write an interface which has two DateTime property and make your Entities inherit them.

interface IEntityDate
{
    DateTime AddedDate { set; get;}
    DateTime ModifiedDate { set; get;}
}

class Entity : IEntityDate
{
    public DateTime AddedDate { set; get;}
    public DateTime ModifiedDate { set; get;}
}

Lelalelah

well entities are created by T4. Those entities are not derived from any interface. So i guess i have to modify that T4, right? – Highchair

@Highchair My bad! if you are using Db first, you can't use interface. I think the only solution is that you add these two columns in your database and then update your model – Lelalelah

Small nitpick, but I would also recommend storing the CreatedDate and ModifedDate using DateTime.UtcNow to avoid dealing with time zone issues. – Sattler

Answer 5

From EF-Core 5x onwards, you can use interceptors and outside the DbContext instead of overriding the SaveChanges method.

Its general format looks like this:

public class AuditableEntitiesInterceptor : SaveChangesInterceptor
{
    public override InterceptionResult<int> SavingChanges(
        DbContextEventData eventData,
        InterceptionResult<int> result)
    {
        if (eventData == null)
        {
            throw new ArgumentNullException(nameof(eventData));
        }

        BeforeSaveTriggers(eventData.Context);
        return result;
    }

     public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData,
        InterceptionResult<int> result,
        CancellationToken cancellationToken = default)
    {
        if (eventData == null)
        {
            throw new ArgumentNullException(nameof(eventData));
        }

        BeforeSaveTriggers(eventData.Context);
        return ValueTask.FromResult(result);
    }

    private void BeforeSaveTriggers(DbContext? context)
    {
        var entries = context?.ChangeTracker
                .Entries()
                .Where(e => e.Entity is BaseEntity && (
                     e.State == EntityState.Added
                     || e.State == EntityState.Modified));

        foreach (var entityEntry in entries)
        {
            ((BaseEntity)entityEntry.Entity).UpdatedDate = DateTimeOffset.UtcNow;

        if (entityEntry.State == EntityState.Added)
        {
            ((BaseEntity)entityEntry.Entity).CreatedDate = DateTimeOffset.UtcNow;
        }
    }
  }
}

You can use of ChangeTracker in BeforeSaveTriggers method to track the entity changes.

Because this interceptor uses dependency injection, it needs to be registered as a service.

services.AddSingleton<AuditableEntitiesInterceptor>();

Then it can be taken from dependency injection in the startup of the program and introduced to the context.

services.AddDbContextPool<ApplicationDbContext>((serviceProvider, optionsBuilder) =>
                optionsBuilder
                    .UseSqlServer(connectionString)
                    .AddInterceptors(serviceProvider.GetRequiredService<AuditableEntitiesInterceptor>()));

Worth noting that this sample BaseEntity uses DateTimeOffset instead of DateTime for a better localization, you can adjust your implementation as needed.

 public class BaseEntity{ 
    public DateTimeOffset CreatedDate { get; set; }
    public DateTimeOffset UpdatedDate { get; set; }    
}

Stucker

Do I need to extend all my models to use the BaseEntity class? – Haskel

Entities that need to have these two dates must inherit from BaseEntity. – Stucker

This answer is the most up-to-date, and leverages dependency injection without needing to override context methods. – Intertwine

Answer 6

I did a similar thing to what @Arash proposed with the public override "Task SaveChangesAsync" with the difference that with my setup I just want to update the entities that actually have a CreatedDate/ModifiedDate properties and this is what I came up with. Very similar to @Arash, but maybe it can help someone. Added a couple of constants EntityCreatedPropertyName and EntityModifiedPropertyName defining the name of the properties so that I would not have to use duplicated strings for those where ever I use them.

    public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken))
    {
        #region Automatically set "CreatedDate" property on an a new entity
        var AddedEntities = ChangeTracker.Entries().Where(E => E.State == EntityState.Added).ToList();

        AddedEntities.ForEach(E =>
        {
            var prop = E.Metadata.FindProperty(EntityCreatedPropertyName);
            if (prop != null)
            {
                E.Property(EntityCreatedPropertyName).CurrentValue = DateTime.Now;
            }
        });
        #endregion

        #region Automatically set "ModifiedDate" property on an a new entity
        var EditedEntities = ChangeTracker.Entries().Where(E => E.State == EntityState.Modified).ToList();

        EditedEntities.ForEach(E =>
        {
            var prop = E.Metadata.FindProperty(EntityModifiedPropertyName);
            if (prop != null)
            {
                E.Property(EntityModifiedPropertyName).CurrentValue = DateTime.Now;
            }
        });
        #endregion

        return base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
    }

Recondition

Answer 7

Here's how I solved in EF Core 2.1:

I have a base model which inherits an interface similar to:

public interface IAuditableModel
{
    DateTime Created { get; }
    DateTime? Updated { get; set; }
}

public class BaseModel : IAuditableModel
{
    public BaseModel()
    {
    }

    public int Id { get; set; }

    public DateTime Created { get; private set; }

    public DateTime? Updated { get; set; }
}

Then, in my DbContext:

public override int SaveChanges()
    {
        var added = ChangeTracker.Entries<IAuditableModel>().Where(E => E.State == EntityState.Added).ToList();

        added.ForEach(E =>
        {
            E.Property(x => x.Created).CurrentValue = DateTime.UtcNow;
            E.Property(x => x.Created).IsModified = true;
        });

        var modified = ChangeTracker.Entries<IAuditableModel>().Where(E => E.State == EntityState.Modified).ToList();

        modified.ForEach(E =>
        {
            E.Property(x => x.Updated).CurrentValue = DateTime.UtcNow;
            E.Property(x => x.Updated).IsModified = true;

            E.Property(x => x.Created).CurrentValue = E.Property(x => x.Created).OriginalValue;
            E.Property(x => x.Created).IsModified = false;
        });

        return base.SaveChanges();
    }

This allows you to avoid hard coding any property names, and lets you flexibly apply the specific interface to models of your choosing. In my case I want this on most models so I applied to my BaseModel which all other models inherit from.

As well I was finding on my views that edited these models the created timestamp was getting wiped out since I was not loading it onto the page. As a result when the model was bound on postback that property was erased setting my created timestamps to 0 effectively.

Potsherd

Answer 8

Here's one of the more generic ways to do it.

Configure entity like this: Don't forget to add "SetAfterSaveBehavior AND SetBeforeSaveBehavior " to avoid throwing an exception by inserting a null value

builder.Property(p => p.ModifiedDate)
  .HasValueGenerator<DateTimeGenerator>()
  .ValueGeneratedOnAddOrUpdate()
  .Metadata.SetAfterSaveBehavior(PropertySaveBehavior.Save);            
builder.Property(p => p.ModifiedDate)
  .Metadata.SetBeforeSaveBehavior(PropertySaveBehavior.Save);

how to write the class DateTimeGenerator :

internal class DateTimeGenerator : ValueGenerator<DateTime>
{
    public override bool GeneratesTemporaryValues => false;

    public override DateTime Next(EntityEntry entry)
    {
        if (entry is null)
        {
            throw new ArgumentNullException(nameof(entry));
        }

        return DateTime.Now;
    }
}

how to write the class DbContext

public override int SaveChanges(bool acceptAllChangesOnSuccess)
{
  GenerateOnUpdate();
  return base.SaveChanges(acceptAllChangesOnSuccess);
}

public override Task<int> SaveChangesAsync(
  bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default)
  {
    GenerateOnUpdate();
    return base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
  }

  private void GenerateOnUpdate()
  {
    foreach (EntityEntry entityEntry in ChangeTracker.Entries())
    {
      foreach (PropertyEntry propertyEntry in entityEntry.Properties)
      {
        IProperty property = propertyEntry.Metadata;
        Func<IProperty, IEntityType, ValueGenerator> valueGeneratorFactory = property.GetValueGeneratorFactory();
        bool generatedOnUpdate = (property.ValueGenerated & ValueGenerated.OnUpdate) == ValueGenerated.OnUpdate;
        
        if (!generatedOnUpdate || valueGeneratorFactory == null)
        {
          continue;
        }

        ValueGenerator valueGenerator = valueGeneratorFactory.Invoke(
          property,
          entityEntry.Metadata
        );                    
        propertyEntry.CurrentValue = valueGenerator.Next(entityEntry);
      }
    }
  }

Chinchilla

Answer 9

A variant of Kahbazi's answer, but using nameof() instead of a magic value for the property name. Depending on your usecase you may want to change DateTime.Now to DateTime.UtcNow.

public void Save() {
    var entitiesToCreate = _context.ChangeTracker.Entries().Where(entity => entity.State == EntityState.Added).ToList();
    var entitiesToUpdate = _context.ChangeTracker.Entries().Where(entity => entity.State == EntityState.Modified).ToList();

    entitiesToCreate.ForEach(entity => entity.Property(nameof(Entity.CreatedDate)).CurrentValue = DateTime.Now);
    entitiesToUpdate.ForEach(entity => entity.Property(nameof(Entity.UpdatedDate)).CurrentValue = DateTime.Now);

    _context.SaveChanges();
}

The underlying Entity looks like this:

public abstract class Entity : IEntity {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int? Id { get; set; }

    public DateTime CreatedDate { get; set; }
    public DateTime? UpdatedDate { get; set; }

    [Required]
    public bool Deleted { get; set; } = false;

    // ConcurrencyToken
    [Timestamp]
    public byte[]? RowVersion { get; set; } = BitConverter.GetBytes(DateTime.Now.ToBinary());
}

Blount