Logo ByteAether

Building an Enterprise Data Access Layer: Automated User Auditing and Series Wrap-up

2025
9 minutes

Welcome to the seventh and final article in our series on building an enterprise-grade Data Access Layer (DAL) in C#. Over the last six posts, we have methodically built a robust, automated, and secure DAL using a database-first philosophy with C# and Linq2Db.

We've tackled soft-deletes, timestamp auditing, multi-tenancy, and even complex projected row-level security. In our very first article, we set a high bar for the capabilities our DAL must provide. Today, we implement the final missing piece: automated user auditing (CreatedByUser / ModifiedByUser).

Once that's in place, we'll hold a full retrospective to see how our final architecture stacks up against our original goals, and finally, we'll look at what challenges lie beyond this series.

Implementing User Auditing

So far, our audit fields (CreatedAt, ModifiedAt) tell us when a record was changed. User auditing completes the picture by telling us who made that change. This is a common requirement for compliance, traceability, and debugging.

Our goal is to automatically populate CreatedByUserId on insert and ModifiedByUserId on insert and update, using the UserId from our request-scoped context. We will follow the exact same pattern of "behavioral interfaces" that we established in Part 3 (Auditing) and Part 4 (Soft-Delete).

The Schema and Interface Contracts

Following our database-first approach, we first update the database schema. We add created_by_user_id ulid not null and modified_by_user_id ulid not null columns to the post and comment tables, both with foreign keys referencing the user table.

Next, we define the C# contracts in DAL.Base/EntityBehavior/:

namespace DAL.Base.EntityBehavior;

public interface IUserCreatable
{
	Ulid CreatedByUserId { get; set; }
}
namespace DAL.Base.EntityBehavior;

public interface IUserModifiable
{
	Ulid ModifiedByUserId { get; set; }
}

These simple interfaces define the properties our DAL will automatically manage.

Automated Scaffolding

With the database and interfaces in place, we update our custom scaffolding interceptor (DAL.ScaffoldInterceptor/Interceptor.cs).

Just as it was configured to detect created_at and attach ICreatable, we've now taught it to look for created_by_user_id and modified_by_user_id columns. When it finds them, it automatically adds IUserCreatable and IUserModifiable to the generated partial entity classes in DbCtx.generated.cs.

This is the power of our architecture: the database schema remains the single source of truth. A schema change, followed by a re-scaffold, is all that's needed to make the C# code aware of this new behavior.

Updating the CRUD Extensions

The final step is to update our CrudExtensions.cs file to enforce the logic. We need to fetch the current user's ID from our IDbCtx (which we added in Part 6) and assign it.

We update CreateAsync, ModifyAsync (for single entity), and ModifyAsync (for fluent queries) to handle these new interfaces.

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

					// NEW: User Create Auditing
					if (
						e is IUserCreatable userCreatable
						&& userCreatable.CreatedByUserId == default
					)
					{
						userCreatable.CreatedByUserId = ctx.Attributes.UserId ?? Ulid.Empty;
					}

					// Timestamp Auditing (from Part 3)
					if (
						e is IModifiable updateable
						&& updateable.ModifiedAt == default
					)
					{
						updateable.ModifiedAt = DateTime.UtcNow;
					}

					// NEW: User Modify Auditing
					if (
						e is IUserModifiable userUpdateable
						&& userUpdateable.ModifiedByUserId == default
					)
					{
						userUpdateable.ModifiedByUserId = ctx.Attributes.UserId ?? Ulid.Empty;
					}

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

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

	// NEW: User Modify Auditing
	if (entity is IUserModifiable userUpdateable)
	{
		userUpdateable.ModifiedByUserId = ctx.Attributes.UserId ?? Ulid.Empty;
	}

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

The IUpdatable Nuance

The fluent ModifyAsync extension (which operates on IUpdatable<T>) presents a unique challenge. This method is what allows us to write code like ctx.GetTable<Post>().Where(p => p.Id == id).ModifyAsync().

To inject the ModifiedByUserId value, we need the IDbCtx instance to get the ctx.Attributes.UserId. However, the IUpdatable<T> (which is essentially an IQueryable) doesn't expose its parent context directly.

To solve this, we use a helper provided by Linq2Db:

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

	// NEW: User Modify Auditing
	if (typeof(IUserModifiable).IsAssignableFrom(typeof(T)))
	{
		// Get the DbCtx from the query
		var dbCtx = Internals.GetDataContext(source) as IDbCtx;
		source = source.Set(
			x => Sql.Property<Ulid>(x, nameof(IUserModifiable.ModifiedByUserId)),
			dbCtx?.Attributes.UserId ?? Ulid.Empty
		);
	}

	return source.UpdateAsync(cancellationToken);
}

The key part is (LinqToDB.Internal.Linq.)Internals.GetDataContext(source). While using an Internal namespace is generally something to be wary of, it is a pragmatic and necessary solution in this advanced scenario. It allows us to bridge our fluent query API with our request-scoped context, ensuring user auditing is applied even during batch UPDATE operations.

And with that, our final automated behavior is implemented.

The Result: A Fully Automated Query

To see all our work come together, let's look at the simple ModifyAsync call from our Program.cs that updates a Comment:

// Try to update a protected comment entity
await ctx.GetTable<Comment>()
	.Set(x => x.Content, "asd123")
	.ModifyAsync();

When this C# code executes, our DAL intercepts it and, using all the logic from this series, generates the following comprehensive SQL query:

UPDATE
	[comment]
SET
	[content] = 'asd123',
	[modified_at] = '2025-11-12 15:31:11.972',
	[modified_by_user_id] = X'019A78B1378CC2EC0F1736EE8D4F1E8F'
FROM
	[post] [a_Post],
	[user] [a_User],
	[permission] [b]
WHERE
	[a_User].[removed_at] IS NULL AND
	[a_User].[tenant_id] = X'019A78B13787E6060BF9BA9A51DAA2C6' AND
	[a_Post].[removed_at] IS NULL AND
	[comment].[removed_at] IS NULL AND
	[comment].[post_id] = [a_Post].[id] AND
	[a_Post].[user_id] = [a_User].[id] AND
	[b].[object_id] = [a_Post].[id] AND
	[b].[subject_id] = X'019A78B1378CC2EC0F1736EE8D4F1E8F'

This single UPDATE statement perfectly demonstrates our entire architecture working in harmony. The SET clause not only updates the content but also automatically injects modified_at (from Part 3) and our new modified_by_user_id field.

Furthermore, the WHERE clause is a composition of all our global filters:

  • Soft-Delete (removed_at IS NULL) on user, post, and comment.
  • Multi-Tenancy (tenant_id = ...) on the user table (a projected filter).
  • Row-Level Security ([b].[object_id] = ...) by joining the permission table (another projected filter).

This is the automation we set out to build, all abstracted from the developer who just called .ModifyAsync().

Achieving Our Goals

In our first article, we defined a clear set of requirements for an enterprise DAL. Our mission was to automate cross-cutting concerns to prevent errors and reduce technical debt. Let's review our original checklist.

  • Soft-Delete:

    • Goal: Automatically filter out records marked with a DeletedAt timestamp on all read operations.
    • Achieved: Yes. In Part 4, we implemented IRemovable and a composable global query filter system. This system automatically and ubiquitously applies the WHERE [removed_at] IS NULL clause to all queries, including joins. We also overrode DeleteAsync to transparently convert deletes into updates.

  • Timestamp Auditing:

    • Goal: Automatically populate CreatedAt and ModifiedAt timestamps.
    • Achieved: Yes. In Part 3, we used ICreatable and IModifiable interfaces and CrudExtensions to inject the current timestamp during create and modify operations.

  • User Auditing:

    • Goal: Automatically populate CreatedByUserId and ModifiedByUserId with the current user's ID.
    • Achieved: Yes. We just completed this in the first section of this article, using the exact same robust pattern (IUserCreatable, IUserModifiable).

  • Multi-Tenancy Filtering:

    • Goal: Automatically inject a WHERE TenantId = [CurrentTenantId] clause for all tenant-owned entities.
    • Achieved: Yes. In Part 5, we leveraged our composable filter system by introducing the ITenanted interface. This filter reads the TenantId from our request-scoped DbCtxAttributes and applies the filter.

  • Projected Data Support:

    • Goal: Resolve contextual IDs (like TenantId or permission IDs) even when they are on a related entity.
    • Achieved: Yes. This was a critical success and a key differentiator for Linq2Db.
      1. Projected Tenancy: In Part 5, our Post entity implemented ITenanted by defining its TenantId via an [ExpressionMethod] that navigated to its parent User (x => x.User.TenantId).
      2. Projected Permissions: In Part 6, our Comment entity implemented IProtected by projecting its permission ObjectId from its parent Post (x => x.Post.Id).
    • In both cases, Linq2Db correctly translated these C# expressions into the necessary SQL INNER JOINs and WHERE clauses.

  • Entity-Based Permissions (RLS):

    • Goal: Automatically filter rows that the current user is not authorized to see.
    • Achieved: Yes. In Part 6, we built our final and most complex filter, IProtected. This filter joins against the permission table using the current UserId, effectively implementing row-level security transparently.

Verdict: We successfully implemented 100% of our initial goals. The final composed query, which Linq2Db generates by combining all these filters, is a testament to the power of this composable, interface-driven architecture.

Future Ideas

While this series is complete, the work of a systems architect is never truly finished. Our DAL is robust, but it exists within a larger ecosystem. Here are a few topics worth exploring next:

  • Database Migrations and CI Pipelines: We've perfected the runtime behavior of our DAL, but we've manually managed schema changes. A critical next step would be to build a full-fledged CI/CD pipeline. This would involve using a migration tool to manage schema evolution, and building a pipeline that runs tests, applies migrations, performs health checks, and supports automatic rollbacks.
  • Full History Support: In our first article, we deliberately scoped out full history support, settling for soft-deletes and simple audit stamps. For systems with deep compliance or analytical needs (e.g., "what did this record look like on June 1st?"), this isn't enough. A fascinating challenge would be to implement a full temporal history or versioning system directly within the DAL.
  • Replicating this with EF Core: Replicating this architecture in EF Core, .NET's most popular ORM, would be a significant challenge. While its Global Query Filters are identical, necessitating the same composable filter system, other aspects diverge. EF Core's "code-first" preference and reliance on a Change Tracker (Unit of Work) would fundamentally alter our scaffolding and CrudExtensions implementation. The largest obstacle would be projected properties, as EF Core lacks native support for [ExpressionMethod], requiring a deep investigation into community libraries to handle our projected tenancy and RLS.

Conclusion

Thank you for following this series. We began with an ambitious set of goals for a "perfect" DAL, and through seven articles, we've built it piece by piece. We have a Data Access Layer that is truly "enterprise-ready" - it is secure by default, resilient to common errors, and abstracts immense complexity away from the business logic.

The final code for this article, and the entire series, is available on GitHub.

Full Source Code: https://github.com/ByteAether/EnterpriseDal/tree/part7

I hope this series has provided a valuable blueprint for your own advanced data access architectures.

Latest posts in the Enterprise DAL series

Building an Enterprise Data Access Layer: Automated User Auditing and Series Wrap-up
01 December 2025
Building an Enterprise Data Access Layer: Composable Row-Level Security
24 November 2025
Building an Enterprise Data Access Layer: Composable Multi-Tenancy Filtering
11 November 2025