Building an Enterprise Data Access Layer: Automated Auditing

In our previous posts, we laid the foundation for our enterprise Data Access Layer (DAL). We established our core principles: a database-first philosophy, the use of C# and Linq2Db, and a commitment to automating cross-cutting concerns to enhance security and data integrity. We also structured our project, set up a scaffolding interceptor to enrich our auto-generated entities, and chose ULIDs as our primary keys for their performance benefits.

This post will build directly on that foundation. We will implement one of the most common and critical DAL features: automated audit fields. Specifically, we will ensure that CreatedAt and ModifiedAt timestamps are automatically and correctly populated for every entity, without requiring any manual intervention from the developer using the DAL. This automation is key to preventing data inconsistencies and reducing cognitive load on engineers.

A Philosophical Shift: Technical vs. Business-Logical CRUD

Before diving into the implementation, it is important to introduce a conceptual distinction that will guide our design. In software development, we often use the acronym CRUD (Create, Read, Update, Delete) to describe data operations. However, these terms often conflate low-level database actions with high-level business logic.

To build a truly robust DAL, we must separate these concepts:

• Technical CRUD: These are the raw database commands: INSERT, UPDATE, DELETE. They represent the fundamental operations the database engine can perform. In our DAL, these will be the underlying methods provided by Linq2Db.
• Business-Logical CRUD: These are higher-level abstractions that represent business intent: CREATE, MODIFY, REMOVE. A single business-logical operation might involve multiple technical operations, validation, and the application of cross-cutting concerns like auditing or security.

Our goal is to expose the business-logical methods (CREATE, MODIFY, REMOVE) to the application's business logic layer, while discouraging the direct use of the technical methods (INSERT, UPDATE, DELETE). By doing so, we guarantee that all our automated rules (such as setting audit fields, enforcing soft-delete, or applying multi-tenancy filters) are always executed.

For now, this separation is a matter of developer discipline. In a future post, we may explore building a custom Roslyn (static code) analyzer that warns an engineer if they attempt to call a technical CRUD method directly from a higher-level service, thus enforcing this architectural pattern at compile time.

Step 1: Defining Behavior with ICreatable and IModifiable

To apply auditing logic selectively, we first need a way to identify which entities require it. We achieve this by defining "entity behavior" interfaces. For this feature, we will add two new interfaces to our DAL.Base/EntityBehavior directory:

• ICreatable: This interface signifies that an entity has a creation timestamp.

  public interface ICreatable : IEntity
  {
  		DateTime CreatedAt { get; set; }
  }

• IModifiable: This interface signifies that an entity has a modification timestamp.

  public interface IModifiable : IEntity
  {
  		DateTime ModifiedAt { get; set; }
  }

These interfaces are simple contracts. An entity implementing ICreatable guarantees it has a CreatedAt property. This allows us to write generic extension methods that can operate on any entity fulfilling this contract.

Step 2: Evolving the Database and Scaffolding

Following our database-first approach, the next step is to update our database schema. We add created_at DATETIME not null and modified_at DATETIME not null columns to all four of our tables: tenant, user, post, and comment.

With the database schema updated, we can enhance our scaffolding interceptor. In addition to adding the IEntity and IIdentifiable<Ulid> interfaces as it did before, the interceptor will now inspect the properties of each table during code generation.

• If a table contains a property named CreatedAt, the interceptor will automatically add ICreatable interface to the generated partial class definition.
• If a table contains a property named ModifiedAt, it will add IModifiable interface.

After running the scaffolding tool, our generated User entity, for example, will now have a signature like this in DbCtx.generated.cs:

public partial class User : IEntity, IIdentifiable<Ulid>, ICreatable, IModifiable
{
		// ... generated properties
}

This powerful combination of a database-first approach and an intelligent interceptor ensures our C# entity definitions always stay synchronized with the database schema and its intended behaviors, without manual intervention.

Step 3: Implementing the Business Logic in CrudExtensions.cs

Now that our entities automatically implement the correct interfaces, we can create the extension methods that constitute our business-logical CREATE and MODIFY operations. We'll place these in a new file, DAL.Base/CrudExtensions.cs.

The CreateAsync Method

The CreateAsync method is an extension on DbCtx. It takes either an entity object or an enumerable of them, automatically sets the audit fields, and then calls the underlying technical INSERT operation.

public static async Task<long> CreateAsync<T>(
	this DbCtx ctx,
	IEnumerable<T> entities,
	CancellationToken cancellationToken = default
) where T : class, IEntity
	=> (
		await ctx.BulkCopyAsync(
			entities.Select(e =>
				{
					if (e is ICreatable creatable && creatable.CreatedAt == default)
					{
						creatable.CreatedAt = DateTime.UtcNow;
					}

					if (e is IModifiable updateable && updateable.ModifiedAt == default)
					{
						updateable.ModifiedAt = DateTime.UtcNow;
					}

					return e;
				}
			),
			cancellationToken
		)
	).RowsCopied;

public static Task<long> CreateAsync<T>(
	this DbCtx ctx,
	T entity,
	CancellationToken cancellationToken = default
) where T : class, IEntity
	=> ctx.CreateAsync([entity], cancellationToken);

By checking the interfaces, these methods correctly handle any entity. If an entity only implements ICreatable, only CreatedAt is set. If it implements both, both timestamps are set.

The ModifyAsync Method

The ModifyAsync methods are slightly different. They are extensions on either DbCtx or IQueryable<T>, designed to work with Linq2Db's fluent update syntax.

public static Task<int> ModifyAsync<T>(
	this DbCtx ctx,
	T entity,
	CancellationToken cancellationToken = default
) where T : class, IEntity
{
	if (entity is IModifiable updateable)
	{
		updateable.ModifiedAt = DateTime.UtcNow;
	}

	return ctx.GetTable<T>().UpdateOptimisticAsync(entity, cancellationToken);
}

public static Task<int> ModifyAsync<T>(
	this IUpdatable<T> source,
	CancellationToken cancellationToken = default
) where T : class
{
	if (typeof(IModifiable).IsAssignableFrom(typeof(T)))
	{
		source = source.Set(
			x => Sql.Property<DateTime>(x, nameof(IModifiable.ModifiedAt)),
			DateTime.UtcNow
		);
	}

	return source.UpdateAsync(cancellationToken);
}

public static Task<int> ModifyAsync<T>(
	this IQueryable<T> source,
	CancellationToken cancellationToken = default
) where T : class
	=> source.AsUpdatable().ModifyAsync(cancellationToken);

These methods cleverly inspect the generic type T. If T implements IModifiable, it injects a .Set() call to update the ModifiedAt field with the current timestamp before executing the final UpdateAsync() call or assigns the current timestamp to an entity's property. This ensures that any update operation expressed through this fluent API is properly audited.

A Note on IQueryExpressionInterceptor

You might be wondering if there is a more integrated way to achieve this within Linq2Db, rather than creating wrapper extension methods. Linq2Db provides a IQueryExpressionInterceptor interface, which is designed to intercept every query expression before its translation into SQL. In theory, this would be the perfect place to inject our auditing logic.

Unfortunately, as of this writing, the implementation of this interceptor is not consistently triggered across all operation types. For example, it does not fire for INSERT operations or for calls to IDataContext.UpdateAsync<T>(), although it does trigger for the fluent IUpdatable<T>.UpdateAsync() method. This inconsistency makes it unsuitable for building a reliable, all-encompassing auditing system.

Should this behavior be fixed in a future version of Linq2Db to reliably intercept all query expressions, it would offer a more elegant solution. We could then centralize our auditing logic in the interceptor and use the standard InsertAsync and UpdateAsync methods directly. This would eliminate the need for our custom CreateAsync and ModifyAsync wrappers and the potential need for a static code analyzer to enforce their use. If such an update occurs, we will certainly explore it in a follow-up article.

Seeing it in Action

With all the pieces in place, we can verify that our implementation works as expected. We can write a small test in Program.cs to create and then modify a User entity. Notice that our C# code never explicitly provides a value for CreatedAt or ModifiedAt.

await ctx.BeginTransactionAsync();

// Create entity
var u = new User
{
		Id = Ulid.New(),
		Username = "asd123"
};
await ctx.CreateAsync(u);

Console.WriteLine(ctx.LastQuery);

// Modify entity
await ctx.GetTable<User>()
		.Where(x => x.Id == u.Id)
		.Set(x => x.Username, "asd1234")
		.ModifyAsync();

Console.WriteLine(ctx.LastQuery);

await ctx.RollbackTransactionAsync();

Executing this code produces the following SQL, captured from Linq2Db's LastQuery property:

INSERT INTO [user]
(
				[id],
				[tenant_id],
				[username],
				[created_at],
				[modified_at]
)
VALUES
(X'0199C31CC424CFC7293147E8A6AE578C',X'00000000000000000000000000000000','asd123','2025-10-08 09:17:46.472','2025-10-08 09:17:46.473')

UPDATE
				[user]
SET
				[username] = 'asd1234',
				[modified_at] = '2025-10-08 09:17:46.506'
WHERE
				[user].[id] = X'0199C31CC424CFC7293147E8A6AE578C'

The output confirms our success. The INSERT statement correctly includes values for both created_at and modified_at. The UPDATE statement automatically includes the SET clause for modified_at. Our DAL now handles these audit fields transparently, reliably, and without any developer effort.

The complete implementation for this part of the series can be found on GitHub: https://github.com/ByteAether/EnterpriseDal/tree/part3.

Conclusion and Next Steps

In this post, we successfully implemented automated auditing for creation and modification timestamps. We introduced the critical distinction between technical and business-logical CRUD operations, defined entity behaviors using interfaces, and leveraged our scaffolding interceptor to automate their implementation. Finally, we created generic extension methods that apply auditing rules consistently across all relevant entities.

In the next episode, we will tackle another core DAL capability: Soft-Delete. This feature is similar in that it will rely on a new interface, but it introduces an additional layer of complexity: we must also implement a global query filter to automatically exclude soft-deleted records from all read operations, a crucial step in preventing data leakage and ensuring application correctness.