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?

Answer 1

See this answer for .NET Core:

https://stackoverflow.com/a/64824067/3850405

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();
    }
    
    ...

Answer 2

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.

Answer 3

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.

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;}
}

Answer 5

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);
    }

Answer 6

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
{

private readonly IHttpContextAccessor _httpContextAccessor;
private readonly ILogger<AuditableEntitiesInterceptor> _logger;

public AuditableEntitiesInterceptor(
    IHttpContextAccessor httpContextAccessor,
    ILogger<AuditableEntitiesInterceptor> logger)
{
    _httpContextAccessor = httpContextAccessor ?? throw new ArgumentNullException(nameof(httpContextAccessor));
    _logger = logger ?? throw new ArgumentNullException(nameof(logger));
}

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>()));

This is the BaseEntity in which DateTimeOffset are used instead of DateTime for a better localization.

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

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.

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);
      }
    }
  }

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());
}