How to implement “CreatedAt” and “UpdatedAt” timestamps in Entity Framework Core?

If you’re starting a new .NET project with Entity Framework Core, or have onboarded to an existing one, chances are you will need to introduce a new entity or two. Including a pair of audit timestamps, indicating when an entity was saved and updated, is a commonly implemented great idea.

However, some questions might pop up: should I set the values on the service side or on database side? Should I refresh the “updated” value in the business logic? What is the best way to implement “CreatedAt” and “UpdatedAt” timestamps in Entity Framework Core?

Let’s dive right in.

Weather Forecasting service provides an API to request forecast calculation, which is saved in an SQLite database. Forecasts are later accessible by an ID or in bulk. Complete code of the example is available in the repository.

Implementation options

If you already use Entity Framework Core, chances are you don’t do much of manual database configuration — EF Core handles many things for you. It makes sense to put all the configuration as close as possible, so the first option is to implement “CreatedAt” and “UpdatedAt" on the database client.

On another hand, if the database is accessed by many services, or EF Core is a convenience layer on top of an established database setup, it might make sense put the database server in control of the audit timestamp management.

Client-side value generation

Client-side value generation is basically setting CreatedAt and UpdatedAt to proper values before sending the data to the database. One might feel inclined to do it like this:

Snippets in this section are not part of the example repository

var forecast = new WeatherForecast
{
    CreatedAt = DateTimeOffset.UtcNow
    // rest omitted
};

await dbContext.WeatherForecasts.AddAsync(forecast);
await dbContext.SaveChangesAsync();

However, this approach doesn’t scale very well (in developer’s productivity sense). Think of all the places where you will save an entity into the database! The more entities you have, the more is the mental overhead to keep up with these inline value initialisations. There might be many places where an entity is inserted or updated.

To resolve this issue, we need to centralise the way we set these values. First candidate for that are Repository implementations:

public class WeatherForecastRepository
{
    public void Add(WeatherForecast forecast)
    {
        forecast.CreatedAt = DateTimeOffset.UtcNow;
        // rest omitted
    }

    public void Update(WeatherForecast forecast)
    {
        forecast.UpdatedAt = DateTimeOffset.UtcNow;
    }

    // rest omitted
}

However, this approach still doesn’t scale. You still need to set this value manually, so while the overall number of audit timestamps assignments is now lower, it is still tied to the number of repositories.

Update method is just one line long. More than that, we don’t really need an "Update" method in repositories working on top of EF Core, because EF Core has a change tracker.

A scalable way to manage audit timestamps on client side

A central point of working with the database is DbContext, which has overridable SaveChanges methods:

public class AppDbContext(DbContextOptions<AppDbContext> options): DbContext(options)
{
    // rest omitted

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

Here, we can access the change tracker to check which entities were created or updated, and set the audit timestamps accordingly. To be able to set the audit timestamps to the entities without resorting to reflection or type pattern matching, introduce an interface like this one:

public interface IEntity
{
    public long Id { get; set; }
    public DateTimeOffset CreatedAt { get; set; }
    public DateTimeOffset? UpdatedAt { get; set; }
}

It’s a great place to add shared properties based on your use cases. For example, you might want to use a GUID-typed ID, or add a soft delete flag like bool IsDeleted.

Make your entities inherit from this interface, and you will be able to do update the timestamps like so:

public override int SaveChanges()
{
    foreach (var entity in ChangeTracker.Entries()
                     .Where(e => e.State == EntityState.Added)
                     .Select(e => e.Entity)
                     .OfType<IEntity>())
    {
        entity.CreatedAt = DateTimeOffset.UtcNow;
    }

    foreach (var entity in ChangeTracker.Entries()
                 .Where(e => e.State == EntityState.Modified)
                 .Select(e => e.Entity)
                 .OfType<IEntity>())
    {
        entity.UpdatedAt = DateTimeOffset.UtcNow;
    }

    return base.SaveChanges();
}

In the LINQ method chains all entries that were added or modified are selected. Then, only the ones implementing IEntity are selected.

It makes sense to override other variations as well, such as SaveChangesAsync:

public class AppDbContext(DbContextOptions<AppDbContext> options): DbContext(options)
{
    // rest omitted 

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

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

    private void SetAuditTimestamps()
    {
        foreach (var entity in ChangeTracker.Entries()
                     .Where(e => e.State == EntityState.Added)
                     .Select(e => e.Entity)
                     .OfType<IEntity>())
        {
            entity.CreatedAt = DateTimeOffset.UtcNow;
        }

        foreach (var entity in ChangeTracker.Entries()
                     .Where(e => e.State == EntityState.Modified)
                     .Select(e => e.Entity)
                     .OfType<IEntity>())
        {
            entity.UpdatedAt = DateTimeOffset.UtcNow;
        }
    }
}

This simple solution addresses the scalability concern: no matter how many entities you have, as long as they implement IEntity interface, the audit timestamps will be set correctly before committing to the database.

However, if multiple projects connect to the same database with other database clients, some coordination will be needed to sync the approach.

Another downside of this solution is that if you forget to override the SaveChanges overload you have used, you will lose some data. This is remedied by simple testing, and in my experience happens early on in the development. On the other hand, there is not too many SaveChanges variations to cover even from the start. You implement this solution, make it work, and forget about it.

Managing audit timestamps on the database side

You can set up your database to assign CreatedAt and UpdatedAt values on the database side. For CreatedAt, considering you are using a mainstream relational database, it is enough to set a default value of CURRENT_TIMESTAMP.

SQL features differ by the database, part 1
You might be more familiar with functions like GETDATE() or NOW() if you use Microsoft SQL Server or PostgreSQL, but CURRENT_TIMESTAMP function is defined in the SQL standard (e.g. SQL:2023), so it should be safe to use in any compliant database.

You can set this up with Entity Framework Core:

public class AppDbContext(DbContextOptions<AppDbContext> options): DbContext(options)
{
    public DbSet<WeatherForecast> WeatherForecasts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // rest omitted 

        modelBuilder
            .Entity<WeatherForecast>()
            .Property(e => e.CreatedAt).HasDefaultValueSql("CURRENT_TIMESTAMP");
    }
}

This means that whenever a record is inserted into the WeatherForecasts table, the current database time will be saved in CreatedAt. This is potentially risky — if your app server and database server are set up with different time zones, you might get incorrect dates. This is fixed by adding AT TIME ZONE ‘UTC’, or using an equivalent construct of your database of choice.

SQL features differ by the database, part 2
CURRENT_TIMESTAMP returns current time with a timezone in PostgreSQL. In SQLite, which is used in the example project, it returns time in UTC, regardless of the system timezone settings. Check your database’s documentation.

For the UpdatedAt timestamp, we would need to define a database trigger. In SQLite, it could be something like this:

CREATE TRIGGER SetUpdatedAt AFTER UPDATE ON WeatherForecasts
BEGIN
  UPDATE WeatherForecasts SET UpdatedAt = CURRENT_TIMESTAMP 
  WHERE Id = OLD.Id;
END

This is non-production code!
This trigger is intentionally implemented as simple as possible. By default, SQLite has recursion disabled for triggers. It might be different in your database.

You can even set this trigger up as part of an EF Core migration. Simply add a migration (might be an empty one), and use migrationBuilder.Sql() method, which will execute any SQL during the migration:

public partial class AddSetUpdatedAttrigger : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder
            .Sql(/* Trigger initialization omitted */);
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder
            .Sql(/* Trigger removal omitted */);
    }
}

Now UpdatedAt will be set to CURRENT_TIMESTAMP even if you manually insert a record.

However, this approach has some downsides to it.

Downsides of implementing CreatedAt and UpdatedAt on database side

Just like managing audit timestamps from a repository, the complexity of this approach depends on the number of tables — you will have to create a trigger like SetUpdatedAt for each new table. In some databases, event or DDL triggers allow to automate the process, but I will leave that as an exercise to the reader.

Another issue is the reduced portability of the project. Even though chances of moving from one database solution to another in a production system are quite low, there are cases when portability is still key. For instance, it is quite convenient to start developing with an SQLite database, and switch to PostgreSQL later on. With this setup, you will need to reimplement triggers for the new database of choice.

If you choose to set this up via EF Core, note that writing raw SQL in C# code has another implication — you might miss any mistakes in your SQL — until after the migration was attempted to be applied.

Client-side vs database-side — what to choose?

With client-side audit timestamps generation, you get a portable, contained, scalable solution that's easy to implement and manage. However, timestamps won’t be added if a record is added manually, and you will have to harmonise the timestamp management approach if the database is shared among multiple projects, and cover all of your SaveChanges use cases.

More than that, you might include even more auditing options, such as writing into an event log table, in a flexible way.

Database-side audit timestamps management is a more complicated setup, which involves database-specific SQL implementations, reducing portability. It is even possible to make this solution scalable by writing event or DDL triggers that would create a trigger for each table.

When considering these approaches, it’s useful to consider these questions among others:

• Is there more than one project (separate implementations, no shared packages) using the same database?
  • Solution: synchronize approaches or move to database-side
• How high is the probability of someone directly inserting data into the database on a regular basis?
  • Solution: move to database-side for regularly updated databases, or set timestamps manually for occasional commands
• Would it be comfortable for the developer to dive more into SQL as opposed to the code-first approach?
• How important is the portability of the setup?
  • Alternatively or additionally: how mature is the project?
  • Solution: go with the client-side if portability is important, or until the final decision on the production database is made

There is no single best solution. Both approaches have their pros and cons.

Alternatively, you might take a progressive approach. If there are CreatedAt and UpdatedAt assignments scattered across the project, then a great first step would be to centralise the management as described in the client-side management approach. Then, if needed, this setup can be moved further to the database side, if needed.

Approaching this problem progressively helps to identify real needs, minimising risks of losing data or functionality.

If you need a more complex audit setup, it might be worth looking into established libraries and tools.

Whatever is your choice, make sure that it’s a pragmatic and practical one.

Links

• Example repository on Monohub
• Audit.NET -- a dedicated library for comprehensive audit implementation.