Building an Enterprise DAL: Automated Auditing for Data Integrity

Why You Need Automated Auditing

Manual data auditing is a liability. Relying on every engineer to remember to set CreatedAt or update ModifiedAt on every data operation introduces bugs and data inconsistencies. A professional Data Access Layer (DAL) must completely automate this process.

This post, part of our Enterprise DAL series, outlines a robust, database-first architecture using C# and Linq2Db to solve this problem transparently and reliably.

Architectural Foundation: Separate CRUD Concerns

We begin by establishing a foundational principle: The application's business logic layer should never use raw database commands.

• Technical CRUD: The raw database operations (INSERT, UPDATE).
• Business-Logical CRUD: The high-level operations (CREATE, MODIFY) that wrap the technical commands and apply cross-cutting rules like auditing.

By forcing interaction through the Business-Logical methods, we guarantee that our audit logic runs every time.

Three Pillars of Automation

1. Behavior Interfaces

We define simple contracts to identify entities that require auditing:

public interface ICreatable { /* ... */ }
public interface IModifiable { /* ... */ }

These interfaces allow us to write generic, type-safe C# logic.

2. Intelligent Database Synchronization

Following a database-first methodology, we ensure our tables have created_at and modified_at columns. The key tool is a scaffolding interceptor.

This interceptor automatically inspects the database schema during code generation. If a table has a created_at column, it automatically adds the ICreatable interface to the generated C# entity partial class. This ensures the C# code is always in sync with the database and ready for our generic logic.

3. Enforcing Logic with Extension Methods

We implement the Business-Logical operations as extension methods on our DbCtx and Linq2Db queries.

• CreateAsync<T>: Before calling the underlying INSERT, this method checks for ICreatable and IModifiable, sets the current timestamp(s), and executes the creation.
• ModifyAsync<T>: Designed for Linq2Db's fluent API, this method cleverly inspects the query type. If it implements IModifiable, it programmatically injects a .Set(x => x.ModifiedAt, DateTime.Now) call before execution.

This makes the auditing completely transparent to the application developer.

Example: A developer calls await query.Set(x => x.Username, "newname").ModifyAsync();. The DAL automatically transforms this into an audited UPDATE statement that includes the modified_at column change.

Conclusion and Full Implementation

This system creates a DAL that is reliable, consistent, and significantly reduces the maintenance burden on your teams. We've proven that automated, architectural enforcement is superior to manual discipline.

For the full C# implementation, including the details of the extension methods and the final SQL output, read the complete blog post.

---

To see the complete C# code implementation, including the extension method details, read the full article on our blog.