Question 89311 in PostgreSQL Question posted in
The official documentation can be found here.

How to get Created and Modified columns to be set automatically using EF Core7 and NPGSQL connector – Postgresql

I have a problem similar to Entity Framework 5: Using DatabaseGeneratedOption.Computed option.

I want to have a creation and modification dates on every table in database. This is what I have now:

public class BaseEntity
{
    [NotNull]
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public Guid Id { get; set; } = Guid.NewGuid();

    //Note: uint timestamp is specific to PostgreSQL database as it maps to it's internal xmin column. For MsSQL you would use byte[] which is the EF standard.
    [Timestamp]
    public uint Version { get; set; }

    public DateTime Created { get; set; } = DateTime.UtcNow;

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime? Modified { get; set; }
}

The Created value works well but the Modified remains unset. I read that [DatabaseGenerated(DatabaseGeneratedOption.Computed)] does not actually guarantee that the update mechanism will be set-up, but I do not know how to set it up using NPGSQL.

I prefer to avoid modifying migrations and using the least amount of fluent configuration I can. If fluent is required, would it be possible to make it apply to all tables with a single command or do I have to apply it to every property separately?

Tags:

2

Answers


  1. 0

    Here is a possible solution for your "Modified" problem. Modify your DbContext like this:

    public class MyContext : DbContext
{
    public override int SaveChanges()
    {
        this.DoCustomEntityPreparations();
        return base.SaveChanges();
    }

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

    public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
    {
        this.DoCustomEntityPreparations();
        return await base.SaveChangesAsync(cancellationToken);
    }

    private void DoCustomEntityPreparations()
    {
            var modifiedEntitiesWithTrackDate = this.ChangeTracker.Entries().Where(c => c.State == EntityState.Modified);
            foreach (var entityEntry in modifiedEntitiesWithTrackDate)
            {
                // Do not confuse "modified" track state and "modified" your property
                if (entityEntry.Properties.Any(c => c.Metadata.Name == "Modified"))
                {
                    entityEntry.Property("Modified").CurrentValue = DateTime.UtcNow;
                }
            }
    }
}

    This will look at all tracked entities that have been modified, search for "Modified" property on them and set it to the current date just before sending it to the database.

    You could do the same automation with "Created" by looking at tracked entities in "Created" state. Again, do not confuse "created" as tracked entity state with your property with the same name.

    Login or Signup to reply.
  2. 0

    The technique of setting the creation time in .NET in your orignial code above will fail in various scenarios. For example, if you instantiate an entity and then attach it on order to modify an existing row in the database, you’ll likely overwrite the creation time in the database with the instance creation time.

    It’s safer in general to let the database manage the creation timestamp simply by using e.g. HasDefaultValueSql(current_timestamp). This also has the advantage of working even for rows inserted outside of EF (e.g. via SQL or non-.NET clients).

    Unfortunately, database typically don’t support automatically updating a timestamp for last modification; the typical way to do this is with a trigger, which really isn’t so bad. If you’d really rather avoid it, then overriding SaveChanges as above will work. However, this again doesn’t work for changes that aren’t done through SaveChanges; importantly, using the new ExecuteUpdate API won’t take this into account.

    For more information, see the EF docs on this – they specifically discuss creation/last modified timestamps.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search