Automatic CreatedAt and UpdatedAt fields OnModelCreating() in ef6

Question

I have CreatedAt and UpdatedAt columns in my User model.

User.cs

public string Name { get; set; }
public DateTime? CreatedAt { get; set; }
public DateTime? UpdatedAt { get; set; }

Requirement

• When we SaveChanges() user records, CreatedAt and UpdatedAt should automatically saved e.g: DateTime.UtcNow
• When I update User record, only UpdatedAt column should get updated to current date time.
• And this should happen automatically for all other models, may be some configuration in OnModelCreating().
• I want this behavior to find latest records from the database, and other places too.
• I am using code first migration approach
• I am using MySQL server, MySql.Data, MySql.Data.Entity.EF6.

UPDATE

I added BaseEntity.cs model

public abstract class BaseEntity
    {
        public DateTime CreatedAt { get; set; }
        public DateTime UpdatedAt { get; set; }
    }

Inheriting User from BaseEntity

public class User : BaseEntity
{
  public int Id { get; set; }
  public int FullName { get; set; }
}

and updated migrations to include defaultValueSql()

AddColumn("dbo.Users", "CreatedAt", c => c.DateTime(nullable: false, precision: 0, defaultValueSql: "NOW()"));
AddColumn("dbo.Users", "UpdatedAt", c => c.DateTime(nullable: false, precision: 0, defaultValueSql: "NOW()"));"

Now, need a way to fix UpdatedAt column in each update.

Answer 1

Finally, Found solution for my problem. Because we can change our database from MySql to postgresql or Ms Sql server, so adding default value using sql query doesn't seems like correct solution.

Here is how I have solved it.

Add Base model

 public abstract class BaseEntity
 {
    public DateTime? CreatedAt { get; set; }
    public DateTime? UpdatedAt { get; set; }
 }

Inherit all your models from this base model, In my case it is User

public class User : BaseEntity
{
  public int Id { get; set; }
  public int FullName { get; set; }
}

don't forget to generate migrations if you are using code first approach. Migration should be simple enough:

Example:

AddColumn("dbo.Users", "CreatedAt", c => c.DateTime(precision: 0));
AddColumn("dbo.Users", "UpdatedAt", c => c.DateTime(precision: 0));

And Final step is to override SaveChanges() and SaveChangesAsync() in your context:

public class MyDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    public override int SaveChanges()
    {
        AddTimestamps();
        return base.SaveChanges();
    }

    public override async Task<int> SaveChangesAsync()
    {
        AddTimestamps();
        return await base.SaveChangesAsync();
    }

    private void AddTimestamps()
    {
        var entities = ChangeTracker.Entries()
            .Where(x => x.Entity is BaseEntity && (x.State == EntityState.Added || x.State == EntityState.Modified));

        foreach (var entity in entities)
        {
            var now = DateTime.UtcNow; // current datetime

            if (entity.State == EntityState.Added)
            {
                ((BaseEntity)entity.Entity).CreatedAt = now;
            }
            ((BaseEntity)entity.Entity).UpdatedAt = now;
        }
    }
}