Entity Framework Core: Generate tracking columns

  • .NET
  • Entity Framework Core
  • .NET

Note: I use the preview version of Entity Framework Core 2.0 (2.0.0-preview2-final). Things may change after the final version is released

When you design a database, you sometimes need to add columns to track when a record changed and who made the change. To clarify, you add the following columns:

  • CreatedAt
  • CreatedBy
  • LastUpdatedAt
  • LastUpdatedBy

You can easily use default values and triggers to handle the CreatedAt and LastUpdatedAt columns. Honestly, creating these triggers is boring and you won't do it by yourself. Moreover, it can be hard to set the user name because it's an application information. Indeed, in a web context, their are only one user that connects to the database. So, you cannot set the value of the tracking columns using the CURRENT_USER function.

Of course, you don't want to bother with this properties. Instead, you want Entity Framework to do it automatically for you. So, the solution is to automatically set the value of these properties before calling SaveChanges or SaveChangesAsync. But there are multiple ways to handle these properties in the model. You can add R/W properties in the model. You can also use R/O properties. Finally, you can use shadow properties. In the last case, the columns are not mapped to a properties in the model. Only Entity Framework is aware of the additional properties. Yes, EF Core is very flexible 😃

Let's implement the 3 ways with Entity Framework!

Read/Write properties

In this case, the entity will has read/write properties. This means, the value of the properties can be changed by your code.

public interface ITrackable
{
    DateTime CreatedAt { get; set; }
    string CreatedBy { get; set; }
    DateTime LastUpdatedAt { get; set; }
    string LastUpdatedBy { get; set; }
}

public class Post : ITrackable
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public DateTime CreatedAt { get; set; }
    public string CreatedBy { get; set; }
    public DateTime LastUpdatedAt { get; set; }
    public string LastUpdatedBy { get; set; }
}

Now, we'll change the default behavior of the SaveChanges method to set the values of the tracking properties. Be careful, there are 2 methods to overload: SaveChanges and the async method SaveChangesAsync. The code iterates over the entities tracked by the context and, depending of the state, set the value of the tracking properties. The code is very simple:

public class BloggingContext : DbContext
{
    public DbSet<Post> Posts { get; set; }

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

    public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken))
    {
        OnBeforeSaving();
        return base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
    }

    private void OnBeforeSaving()
    {
        var entries = ChangeTracker.Entries();
        foreach (var entry in entries)
        {
            if (entry.Entity is ITrackable trackable)
            {
                var now = DateTime.UtcNow;
                var user = GetCurrentUser();
                switch (entry.State)
                {
                    case EntityState.Modified:
                        trackable.LastUpdatedAt = now;
                        trackable.LastUpdatedBy = user;
                        break;

                    case EntityState.Added:
                        trackable.CreatedAt = now;
                        trackable.CreatedBy = user;
                        trackable.LastUpdatedAt = now;
                        trackable.LastUpdatedBy = user;
                        break;
                }
            }
        }
    }

    private string GetCurrentUser()
    {
        return "UserName"; // TODO implement your own logic

        // If you are using ASP.NET Core, you should look at this answer on StackOverflow
        // https://stackoverflow.com/a/48554738/2996339
    }
}

The inconvenient of this method, is the properties have a setter while you don't want to set the value in your code. Let's see how to use read-only properties.

Read-only properties

Entity Framework Core allows to map a column to a field. This way, you can use read-only properties. Let's modify thePost class:

public class Post
{
    private DateTime _createdAt;
    private string _createdBy;
    private DateTime _lastUpdatedAt;
    private string _lastUpdatedBy;

    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public DateTime CreatedAt => _createdAt;
    public string CreatedBy => _createdBy;
    public DateTime LastUpdatedAt => _lastUpdatedAt;
    public string LastUpdatedBy => _lastUpdatedBy;
}

This is a little more verbose, but the properties are now read-only. You need to instruct Entity Framework to use the fields to set the value of the column.

public class BloggingContext : DbContext
{
    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Post>()
            .Property(post => post.CreatedAt)
            .HasField("_createdAt");

        modelBuilder.Entity<Post>()
            .Property(post => post.CreatedBy)
            .HasField("_createdBy");

        modelBuilder.Entity<Post>()
            .Property(post => post.LastUpdatedAt)
            .HasField("_lastUpdatedAt");

        modelBuilder.Entity<Post>()
            .Property(post => post.LastUpdatedBy)
            .HasField("_lastUpdatedBy");
    }
}

You cannot write the OnBeforeSaving method as before because there is not setter. Instead, you can use theChangeTracker to indicate Entity Framework the value to set for the properties. This way there is no need to access the property or the field.

    private void OnBeforeSaving()
    {
        var entries = ChangeTracker.Entries();
        foreach (var entry in entries)
        {
            if (entry.Entity is Post post)
            {
                var now = DateTime.UtcNow;
                var user = GetCurrentUser();
                switch (entry.State)
                {
                    case EntityState.Modified:
                        entry.CurrentValues["LastUpdatedAt"] = now;
                        entry.CurrentValues["LastUpdatedBy"] = user;
                        break;

                    case EntityState.Added:
                        entry.CurrentValues["CreatedAt"] = now;
                        entry.CurrentValues["CreatedBy"] = user;
                        entry.CurrentValues["LastUpdatedAt"] = now;
                        entry.CurrentValues["LastUpdatedBy"] = user;
                        break;
                }
            }
        }
    }
}

I think this way is clean I should be the way to use.

No property

In some case, you may want to set column values without exposing properties in the model. For instance, you may expose the last updated date, but not who made the change. Entity Framework Core can handle this by using shadow properties. Shadow properties are declared in the OnModelCreating method, but does not exist in your classes. You can read and write the value of the shadow properties using the ChangeTracker.

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
}
public class BloggingContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Declare properties
        modelBuilder.Entity<Post>().Property<DateTime>("CreatedAt");
        modelBuilder.Entity<Post>().Property<string>("CreatedBy");
        modelBuilder.Entity<Post>().Property<DateTime>("LastUpdatedAt");
        modelBuilder.Entity<Post>().Property<string>("LastUpdatedBy");
    }

    private void OnBeforeSaving()
    {
        var entries = ChangeTracker.Entries();
        foreach (var entry in entries)
        {
            if (entry.Entity is Post post)
            {
                var now = DateTime.UtcNow;
                var user = GetCurrentUser();
                switch (entry.State)
                {
                    case EntityState.Modified:
                        entry.CurrentValues["LastUpdatedAt"] = now;
                        entry.CurrentValues["LastUpdatedBy"] = user;
                        break;

                    case EntityState.Added:
                        entry.CurrentValues["CreatedAt"] = now;
                        entry.CurrentValues["CreatedBy"] = user;
                        entry.CurrentValues["LastUpdatedAt"] = now;
                        entry.CurrentValues["LastUpdatedBy"] = user;
                        break;
                }
            }
        }
    }
}

Conclusion

Using Entity Framework Core, you can map the database tables to your object model the way you want. This allows to create clean classes and you don't have to expose properties you don't want. Very nice 😃

Do you have a question or a suggestion about this post? Contact me on Twitter or by email!

Follow me:
Enjoy this blog?Buy Me A CoffeeDonate with PayPal

Comments

Holmes Chong -

Thanks. This post and soft-delete is exactly the problem I'm facing now

Ahmed Said -

In the 2nd code snippet, second CreatedAt should be CreatedBy

Gérald Barré -

Thanks for reporting this typo, I've fixed it!

Make Luv -

In first variant on EntityState.Modified CreatedAt in DB will be 0001-01-01 00:00:00.0000000 because CreatedAt not set and has default value.

Habib ul Rehman -

Thank you so much for such a good tracking strategy.