Audit Trail With Entity Framework – Persisting Entity History In Database

We are solving various problems. Sometimes the business is interested in the history of an entity. For example, the business might want to know all the previous prices of a product. Not necessarily how those prices changed, but the exact values on the timeline. So that’s the problem we need to solve. There probably are many approaches that we can use, but the obvious one is EventSourcing. The question is – do you really need it? With all the pros, cons, and consequences? And the other question (more important to me) is – do we have the required competencies to do it effectively to provide the value as fast as we can?

Let’s say that you haven’t used EventSourcing in a production project. Given your professionalism, you decide not to use it this time. First, you’d rather figure it out on a pet project and then, if there’s enough value from that approach, change the solution in the project.

So, what else?

The problem

Before we get into the code, let’s quickly summarise the problem:

  • We’re currently able to set and change the price of a product
  • However, we’d like to know all the previous prices of that product (price history for a product)

And that’s it. Let’s get into the solution.

The solution

In this example we’ll not be using EventSourcing. We’ll use the Audit Trail (aka Audit Log) implemented with Entity Framework.

We’ll keep the price in two tables. In the product table, we’ll keep the actual price of the product. In the ProductPriceHistory table, we’ll keep all the previous prices for that product.

IdNameDescriptionPricePriceCurrencyPictureUri
Table schema representing Product
ProductIdPricePriceCurrencyChangedDate
Table schema representing ProductPriceHistory

Okay, now let’s look at the simplified code which is presenting the idea of this approach for setting the price of the product.

        public async Task SetProductPrice(Guid productId, decimal price, string currency)
        {
            var product = await _context.Products.FirstOrDefaultAsync(x => x.Id == productId, cancellationToken);

            product.SetPrice(price, currency);

            var productPriceHistory = new ProductPriceHistory(product.Id, price, currency, changedDate: DateTime.UtcNow);

            await _context.ProductPriceHistory.AddAsync(productPriceHistory, cancellationToken);
            await _context.SaveChangesAsync();
        }

This way we’ll always have the latest product price in the Product table and the history of the price will be persisted and can be used for UI, analytical purposes, or anything else that the business needs. And it’ll happen during one database transaction.

Quite a simple solution that does a job.

What do you think about this approach? Do you see any obvious improvements?

Let's stay in touch!