Building an Enterprise Data Access Layer: Database and Code Structure

Welcome back to our series on building a robust, enterprise-grade Data Access Layer (DAL) with C# and Linq2Db. In our previous post, we established the core principles and non-negotiable capabilities our DAL must possess, including automatic multi-tenancy filtering, soft-delete, and auditing. Now, we'll begin laying the foundation for this powerful system by defining the database schema and the initial code structure.

This post will explore the deliberate technical choices we have made for this project's foundation. While these decisions may seem straightforward at first glance, each one serves a specific purpose in building a scalable, secure, and maintainable data layer that abstracts complexity away from the business logic.

The Database-First Philosophy

Before we define the structure, let's address the approach. This project is firmly rooted in a database-first philosophy. In my view, the database is a core engineering artifact and should be treated as such, designed and managed using purpose-built database engineering tools. The schema is the source of truth, and our code should be a faithful reflection of that schema.

This approach ensures that database constraints, indexes, and data types are optimized for the database engine itself, rather than being an afterthought generated by an Object-Relational Mapper (ORM). It provides a clear separation of concerns, allowing database administrators and backend engineers to collaborate on a well-defined and performant schema.

Our Core Data Model

For this series, we will build a simple data model to represent a multi-tenant blogging platform. The model consists of four essential tables, each with a clear purpose and relationship to the others.

• tenant: This table represents the top-level entity in our multi-tenant application. Every user belongs to a single tenant, and all data within a tenant is isolated from others. It contains a unique id and a name.
• user: This table holds our user accounts. Each user is associated with a specific tenant via a foreign key (tenant_id). It also contains a username.
• post: A post is a content unit created by a user. It references the user via a foreign key (user_id). A post has a title and the main content.
• comment: This table stores comments on a post. Each comment is linked to a post via a foreign key (post_id) and contains its content.

The relationships are as follows:

• comment.post_id references post.id
• post.user_id references user.id
• user.tenant_id references tenant.id

This simple structure allows us to demonstrate how the DAL can automatically enforce rules like multi-tenancy and permissions by navigating these relationships.

The Power of the ULID

A cornerstone of our database design is the choice of ULID (Universal Unique Lexicographically Sortable Identifier) as the primary key for all tables. A ULID is a 128-bit identifier that combines the uniqueness of a UUID with the sortable properties of a timestamp.

In our C# code, we will use the ByteAether.Ulid library. This library provides a native Ulid type that is specifically designed for this purpose. In the database, ULIDs are stored as 128-bit binary, which is the most efficient representation.

The key advantage of ULIDs is their sortability. Because the first 48 bits of a ULID are a timestamp, when you sort a column of ULIDs, you are also sorting by the order of creation. This is incredibly beneficial for performance on append-only tables, as new records are always written to the end of the table, minimizing fragmentation. It also makes it easy to find the most recent records without a separate timestamp column.

For databases like SQLite, this property allows us to declare our tables WITHOUT ROWID. This SQLite-specific feature, when used with a ULID primary key, removes the default internal 64-bit integer row identifier, making the ULID the sole source of record identity and order. Since ULIDs are inherently sortable, the table is naturally clustered by creation time, which optimizes read operations on the most recent data.

Here is the full SQL schema for our four tables:

create table tenant
(
	id   ulid        not null
		primary key,
	name VARCHAR(64) not null
		unique
)
	without rowid;

create table user
(
	id        ulid        not null
		primary key,
	tenant_id ulid        not null
		references tenant,
	username  VARCHAR(64) not null
)
	without rowid;

create table post
(
	id      ulid        not null
		primary key,
	user_id ulid        not null
		references user,
	title   varchar(64) not null,
	content text        not null
)
	without rowid;

create table comment
(
	id      ulid not null
		primary key,
	post_id ulid not null
		references post,
	content text not null
)
	without rowid;

The Scaffolding and Code Structure

With our database schema defined, we now turn to the C# code. We will use Linq2Db's built-in scaffolding tool to generate our C# entity classes and a database context. However, we will enhance this process with a custom scaffolding interceptor.

Our core code structure will be organized into a few key areas:

DAL.Base

This namespace holds the fundamental interfaces that all of our entities may inherit from. We're keeping it simple for now, with two interfaces:

• IEntity: A simple marker interface that all of our data access layer entities will inherit. While it is empty now, its existence allows us to build generic services and behaviors that can be applied to any entity in the DAL.
• IIdentifiable<T>: An interface that marks any entity that has a property named Id of type T. This is a crucial interface for our core services, as it allows us to build generic logic that can operate on any entity with an identifier. For example, a generic queryable.WhereId() method would require this interface.

DAL.Context

This namespace contains our database context and all generated entity classes. We've made a key architectural decision here: all generated classes will reside in a single file named DbCtx.generated.cs. This simplifies the regeneration process, as you only need to update a single file when the database schema changes.

• DbCtx.generated.cs: This file will be created by the Linq2Db scaffolding tool. It will contain our DbCtx class, as well as partial classes for our Tenant, User, Post, and Comment entities.
• DbCtx.cs: This is the complementary file for our DbCtx.generated.cs. The DbCtx class is a partial class, so we can extend it with our own methods and properties here, leaving the generated file untouched.

A key piece of the DbCtx.cs file is the partial void InitDataContext() method. This method, which is automatically called by the generated constructor in DbCtx.generated.cs, is where we will define the runtime type mapping for our Ulids. This ensures that when Linq2Db reads a ulid type from the database (as a byte array), it is correctly converted into a Ulid C# object, and vice versa. This runtime mapping is the counterpart to the design-time mapping handled by our ScaffoldInterceptor.

DAL.Context.Entity

This namespace is where we will create additional partial classes to extend the functionality of our generated entity classes. For example, we could create a Post.cs file to add a convenience method for getting the post's user from the database.

DAL.ScaffoldInterceptor

This is the "secret sauce" of our scaffolding process. We will create a class that implements ScaffoldInterceptors. This interceptor allows us to hook into the scaffolding process and customize it.

Our custom interceptor will perform two key functions:

1. Interface Injection: It will automatically add our IEntity and IIdentifiable<Ulid> interfaces to the generated partial entity classes.
2. ULID Type Mapping: It will intercept any database column with the ulid type and ensure it is properly mapped to the Ulid type from our C# library in the generated code.
3. Association Naming Convention: We will use the interceptor to enforce a strict and predictable naming convention for association properties. For One-To-Many relationships, we adopt a simple 's' suffix (e.g., a Post entity will have a property named Comments, not Commentaries or Commentss). This deliberate choice avoids the ambiguity and confusion that can arise from irregular pluralization in English (e.g., avoiding a scenario where a Mouse entity might generate a Mice collection). For Many-To-One relationships, we simply use the entity's singular name (e.g., a Comment entity will have a Post property).

The use of partial classes is a core tenet of this approach. It gives us the best of both worlds: we have the convenience of an automatically generated code base that stays in sync with our database schema, while also having the freedom to extend and customize our models and database context without ever touching the generated code.

Looking Ahead

In this post, we've defined the bedrock of our enterprise DAL, from the database schema and the choice of ULID primary keys to the core code structure and our enhanced scaffolding process. We started simple, without implementing audit fields or a permission system. The project's root also includes a simple Program.cs file used for demonstration. This utility file connects to our SQLite database and runs simple LINQ queries (like selecting all users). Crucially, we use it to inspect the generated SQL from our LINQ expressions, rather than executing the queries and reviewing the result set. This allows us to verify that our DAL is correctly translating our C# code into performant SQL.

The full source code for this post, including the scaffolding interceptor and the completed project structure, is available at GitHub.

Note on Dependencies: This entire series and its accompanying code are built on the forthcoming Linq2Db version 6.0. As of the time of writing, this version is only available as a Release Candidate (RC3). To include necessary fixes not yet in the official package, we have manually included a specific build of the Linq2Db NuGet package within the project's /nuget folder. This ensures the code reflects the behavior of the final 6.0 release.

In the next post, we will dive into the details of implementing the crucial audit fields (CreatedAt, CreatedBy, ModifiedAt, and ModifiedBy) and the logic to automatically populate them during data operations. We'll show how the foundation we've built here makes this complex, cross-cutting concern remarkably simple to implement.