Enterprise DAL Final: Automated User Auditing and Architectural Retrospective

This post marks the end of our series on constructing an enterprise-grade Data Access Layer (DAL) in C# with Linq2Db. Our goal was to create a DAL that is secure and resilient by automatically handling crucial cross-cutting concerns.

To achieve compliance and enhance debugging, we implement Automated User Auditing. This feature automatically populates CreatedByUserId and ModifiedByUserId fields with the current user’s unique identifier (Ulid).

The implementation is integrated into our existing interface-driven architecture:

1. Contracts: New interfaces (IUserCreatable, IUserModifiable) define the required properties.
2. Automation: Our custom scaffolding ensures that any entity table with created_by_user_id/modified_by_user_id columns automatically implements these interfaces.
3. Injection Logic: We update our CrudExtensions to inspect the entity on create/modify operations. If the entity implements the auditing interface, the current UserId from the request-scoped context is injected before the database operation executes.

Crucial detail for Architects: We detail a necessary, advanced technique using (LinqToDB.Internal.Linq).Internals.GetDataContext(source) to correctly inject the ModifiedByUserId user ID during fluent batch UPDATE operations (IUpdatable<T>). This ensures auditing integrity across all modification types.

Verdict: 100% Goal Achievement

The final architecture successfully automated every requirement initially set for a perfect enterprise DAL:

• Ubiquitous Filtering: Soft-Delete, Multi-Tenancy, and Row-Level Security are enforced by a highly composable global query filter system, transparently generating the correct SQL joins and WHERE clauses for all read operations.
• Projected Security: The use of Linq2Db’s projection capabilities allows for complex security and tenancy rules to be resolved contextually from related entities, a key differentiator.
• Auditing: Timestamp and User auditing are handled automatically on creation and modification.

This robust DAL abstracts away security and compliance boilerplate, letting developers focus purely on business logic.

For the full implementation details, including source code and an in-depth explanation of the global query filter system, read the complete article on my blog.

Source: DEV Community.