How to automatically populate CreatedDate and ModifiedDate?

Question

I am learning ASP.NET Core MVC and my model is

namespace Joukyuu.Models
{
    public class Passage
    {
        public int PassageId { get; set; }
        public string Contents { get; set; }


        public DateTime CreatedDate { get; set; }
        public DateTime ModifiedDate { get; set; }
    }
}

The Passage table is used to save passages I wrote.

Scenario

• Create view just has one field Contents to input a passage. CreatedDate and ModifiedDate must be automatically set equal by the server (using UTC format).

• Edit view just has one field Contents to edit a passage. ModifiedDate must be automatically set by the server.

Question

What attributes I have to attach to the CreatedDate and ModifiedDate properties to make them automatically populated by the server based on the above scenario?

Solution 1

What attributes I have to attach to the CreatedDate and ModifiedDate properties to make them automatically populated by the server based on the above scenario?

Solution 1)

namespace Joukyuu.Models
{
    public class Passage
    {
        public int PassageId { get; set; }
        public string Contents { get; set; }


        public DateTime CreatedDate { get; set; }
        public DateTime ModifiedDate { get; set; }

       public Passage()
       {          
         this.CreatedDate  = DateTime.UtcNow;
         this.ModifiedDate = DateTime.UtcNow;
       }
    }
}

and by edit you have to change/update it by your self!

Solution 2)

Custom attribute:

[SqlDefaultValue(DefaultValue = "getutcdate()")]
public DateTime CreatedDate { get; set; }

Entity Framework 6 Code first Default value

Solution 3)

with help of Computed:

[Required, DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime CreatedUtc { get; set; 


  "dbo.Products",
            c => new
                {
                    ProductId = c.Int(nullable: false, identity: true),
                    Name = c.String(),
                    CreatedUtc = c.DateTime(nullable: false, defaultValueSql: "GETUTCDATE()"),
                })
            .PrimaryKey(t => t.ProductId);

https://andy.mehalick.com/2014/02/06/ef6-adding-a-created-datetime-column-automatically-with-code-first-migrations/

Solution 4) You can also do this with command interceptor by modifying manually the query.

Solution 5) Use Repository pattern to manage the data creation and set it by CreateNew This is my favour Solution!

https://msdn.microsoft.com/en-us/library/ff649690.aspx

Solution 6) just set it or get in in the UI or in your VM.

---

In Entity Framework Core 1.0 easy:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Passage>()
        .Property(b => b.CreatedDate )
        .HasDefaultValueSql("getdate()");
}

Solution 2

For those who are using the asynchronous system (SaveChangesAsync) and .NET Core, it's better to override the DbContext's SaveChangesAsync method:

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

Also, you can define a base class or an interface for your models with these properties:

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

Solution 3

If your are using Code first you could try this

[Required, DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime CreatedDate { get; set; }

On Migration

AddColumn("Passage", "CreatedDate", n => n.DateTime(nullable: false, defaultValueSql: "GETDATE()"));

More reference here,similar answer

Or you can global override the saveChanges Note* This will affect on the entire model if you have the CreatedDate field

public override int SaveChanges()
{
  DateTime saveTime = DateTime.Now;
  foreach (var entry in this.ChangeTracker.Entries()
      .Where(e => e.State == (EntityState) System.Data.EntityState.Added))
   {
     if (entry.Property("CreatedDate").CurrentValue == null)
       entry.Property("CreatedDate").CurrentValue = saveTime;
   }
   return base.SaveChanges();  
}

Solution 4

Solution for CreationDate in PostgreSQL:

builder.Property(e => e.CreationDate)
  .HasColumnType("timestamp without time zone")
  .HasDefaultValueSql("NOW()")
  .ValueGeneratedOnAdd();

via: https://www.npgsql.org/efcore/modeling/generated-properties.html#guiduuid-generation Unfortunately there is not solution for update event

Solution 5

An alternative to using SaveChangesAsync is you can create database triggers for the respective database. I made a gist with some helper functions for mssql, postgress, sqlite, and mysql. Here is a paste of the code.

There is an interesting discussion here of having database logic vs application logic. Personally, having the database enforce the updated_date column update has less lines in my code. One argument they also made is that if it's your is changed using a different service these database integrity constraints will still apply at the database level.

I've been using this class below whenever I do ef core data migrations to add custom updated column triggers on table and column updates.

using Microsoft.EntityFrameworkCore.Metadata;
using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata;

namespace ProjectNamespace.Api.Utils
{
    public class DatabaseTools
    {
        public class DatabaseKeys
        {
            public string ValueGenerationStrategy;
            public object SerialColumn;
            public string DEFAULTDATE_CREATE;
            public string DEFAULTDATE_UPDATE;
            public string dateTime;
            public bool UpdateDateTrigger = true;
            public string ProviderName { get; set; }
        }
        /*
        Notes:
        can check if updateDateTrigger needed for dataprovider and add update triggers
        if (dbKeys.UpdateDateTrigger)
            {
                migrationBuilder.Sql(DatabaseTools.getUpdateDateTrigger(dbKeys.ProviderName, "TG_roles_updated_at", "roles", "updated_at"));
            }
        if postgress don't forget to drop the generated functions for each of the different named update columns (updated_at, modified_date) at the end of the migration E.g.
        protected override void Down(MigrationBuilder migrationBuilder){...
        if (dbKeys.ProviderName == "postgress")
            {
                migrationBuilder.Sql(@"
                    DROP FUNCTION update_" + "updated_at" + @"_column();
                    ");
            }
        postgres doesn't support parameters in triggers for new and old keywords.
         */
        /// <summary>
        /// needed for non mysql implementation to update a datetime on a record update
        /// </summary>
        /// <param name="databaseProvider"></param>
        /// <param name="name"></param>
        /// <param name="table"></param>
        /// <param name="column"></param>
        /// <param name="schema"></param>
        /// <param name="id_column"></param>
        /// <returns></returns>
        public static string getUpdateDateTrigger(string databaseProvider, string name, string table, string column, string schema = "", string id_column = "id")
        {
            string updateDateTrigger = null;
            if (!string.IsNullOrEmpty(schema))
                schema = schema + ".";
            switch (databaseProvider)
            {
                case "sqlite":
                    updateDateTrigger = @"
                    CREATE TRIGGER [" + name + @"]  
                        AFTER   
                        UPDATE  
                        ON " + table + @"
                        FOR EACH ROW   
                        WHEN NEW." + column + @" <= OLD." + column + @"  
                    BEGIN  
                        update " + table + @" set " + column + @"=CURRENT_TIMESTAMP where " + id_column + @"=OLD." + id_column + @";  
                    END  
                    ";
                    break;
                case "postgress":
                    updateDateTrigger = @"
                    CREATE OR REPLACE FUNCTION update_" + column + @"_column() RETURNS TRIGGER AS
                    $$
                    BEGIN
                        NEW.""" + column + @""" = now();
                        RETURN NEW;  
                    END;
                    $$ LANGUAGE plpgsql;
                    CREATE TRIGGER " + name + @" 
                    BEFORE UPDATE ON " + schema + @"""" + table + @"""
                    FOR EACH ROW EXECUTE PROCEDURE update_" + column + @"_column();
                    ";
                    break;
                case "sqlserver":
                    updateDateTrigger = @"
                    CREATE TRIGGER " + name + @" 
                    ON " + table + @"
                    AFTER UPDATE AS
                        UPDATE " + table + @"
                        SET " + column + @" = GETDATE()
                        WHERE " + id_column + @" IN (SELECT DISTINCT " + id_column + @" FROM Inserted);
                    ";
                    break;
            }

            return updateDateTrigger;
        }
        /*
        Notes:
        if in OnModelCreating can call as var dbKeys = DatabaseTools.getDatabaseDefaults(this.Database.ProviderName);
        if in a migration call as var dbKeys = DatabaseTools.getDatabaseDefaults(migrationBuilder.ActiveProvider);
        Then update any datetime columns for correct defaults
        created_at = table.Column<DateTime>(type: dbKeys.dateTime, nullable: true, defaultValueSql: dbKeys.DEFAULTDATE_CREATE),
        updated_at = table.Column<DateTime>(type: dbKeys.dateTime, nullable: true, defaultValueSql: dbKeys.DEFAULTDATE_UPDATE)
         */
        /// <summary>
        /// Can get specific formats for different databases. Supports mssql, postgress, sqlite, and mysql.
        /// </summary>
        /// <param name="databaseProvider"></param>
        /// <returns></returns>
        public static DatabaseKeys getDatabaseDefaults(string databaseProvider)
        {
            var dbKeys = new DatabaseKeys();
            dbKeys.dateTime = "datetime";

            switch (databaseProvider)
            {
                case "Microsoft.EntityFrameworkCore.Sqlite":
                case "sqlite":
                    dbKeys.DEFAULTDATE_UPDATE = "datetime('now')";
                    dbKeys.SerialColumn = true;
                    dbKeys.ProviderName = "sqlite";
                    dbKeys.ValueGenerationStrategy = "Sqlite:Autoincrement";
                    break;
                case "postgress":
                case "Npgsql.EntityFrameworkCore.PostgreSQL":
                    dbKeys.DEFAULTDATE_UPDATE = "CURRENT_TIMESTAMP";
                    dbKeys.SerialColumn = NpgsqlValueGenerationStrategy.SerialColumn;
                    dbKeys.ProviderName = "postgress";
                    dbKeys.ValueGenerationStrategy = "Npgsql:ValueGenerationStrategy";
                    dbKeys.dateTime = "timestamp";
                    break;
                case "mysql":
                case "MySql.Data.EntityFrameworkCore":
                    dbKeys.DEFAULTDATE_UPDATE = "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP";
                    dbKeys.DEFAULTDATE_CREATE = "CURRENT_TIMESTAMP";
                    dbKeys.UpdateDateTrigger = false;
                    dbKeys.SerialColumn = true;
                    dbKeys.ProviderName = "mysql";
                    dbKeys.ValueGenerationStrategy = "MySQL:AutoIncrement";
                    break;
                case "sqlserver":
                default:
                    dbKeys.DEFAULTDATE_UPDATE = "(getdate())";
                    dbKeys.SerialColumn = SqlServerValueGenerationStrategy.IdentityColumn;
                    dbKeys.ProviderName = "sqlserver";
                    dbKeys.ValueGenerationStrategy = "SqlServer:ValueGenerationStrategy";
                    break;
            }
            if (string.IsNullOrEmpty(dbKeys.DEFAULTDATE_CREATE))
                dbKeys.DEFAULTDATE_CREATE = dbKeys.DEFAULTDATE_UPDATE;
            return dbKeys;
        }
    }
}

View more solutions