Building an Enterprise Data Access Layer: Composable Multi-Tenancy Filtering

In our previous articles, we established a robust foundation for our enterprise Data Access Layer (DAL). We began with a database-first approach using C# and Linq2Db, then implemented automated auditing for CreatedAt and ModifiedAt timestamps. Most recently, we engineered a powerful, composable global query filter system to handle soft-deletes transparently. This architecture was designed for extensibility, and in this post, we will leverage that investment to tackle one of the most critical cross-cutting concerns in enterprise software: multi-tenancy.

Multi-tenancy ensures that one tenant's data is strictly isolated from another's. A failure in this area is not a minor bug, but a critical security breach. Our goal is to enforce this isolation at the lowest possible level—the DAL—to guarantee that it is impossible for business logic to accidentally query data from the wrong tenant.

We will extend our entity behavior model by introducing an ITenanted interface, update our database context to be tenant-aware, and enhance our scaffolding interceptor for automation. Crucially, we will also solve the complex problem of "projected tenancy," where an entity's tenant affiliation is derived from a related entity.

The complete source code for this part is available on GitHub: https://github.com/ByteAether/EnterpriseDal/tree/part5.

1. Establishing Tenant Context in the DAL

Before our DAL can filter by tenant, it needs to know the identity of the current tenant for any given operation. This context is typically derived from an HTTP request, a message queue header, or a similar scope. We need a clean mechanism to pass this information down to our database context.

Previously, our IDbCtx was a simple marker interface. We will now extend it to carry request-scoped attributes, starting with the TenantId.

File: DAL.Base/IDbCtx.cs

namespace DAL.Base;

public interface IDbCtx : IDataContext
{
	DbCtxAttributes Attributes { get; set; }

	public record DbCtxAttributes(Ulid TenantId = default);
}

By adding the Attributes property, we provide a dedicated, extensible home for contextual data. We use a C# record for DbCtxAttributes to create a simple, immutable data carrier. The IDbCtx implementation (our DbCtx class) will be responsible for initializing this property. In a real application, this would be done via dependency injection at the beginning of a request scope, making the current TenantId available for the lifetime of that request.

For our demonstration, we will manually set this value in Program.cs before executing any queries. This simulates how a tenant's context would be established at the start of an operation.

File: App/Program.cs (Addition)

var tenantId = Ulid.New();
ctx.Attributes = new(TenantId: tenantId);

While we use a new Ulid here, the key is that this specific value will later appear in the generated SQL WHERE clause, confirming that our filter is using the context we've provided.

2. Defining the Tenancy Contract: The ITenanted Interface

Following the pattern established with ICreatable and IRemovable, we will define tenancy as an entity behavior through a new interface. This interface serves two purposes: it enforces a contract that the entity must expose a TenantId, and it hooks into our global filter system.

File: DAL.Base/EntityBehavior/ITenanted.cs

namespace DAL.Base.EntityBehavior;

[EntityFilter<ITenanted>(nameof(Filter))]
public interface ITenanted : IEntity
{
	Ulid TenantId { get; }

	private static IQueryable<T> Filter<T>(IQueryable<T> q, IDbCtx ctx) where T : ITenanted
		=> q.Where(x => x.TenantId == ctx.Attributes.TenantId);
}

This implementation is concise but powerful, directly leveraging our previous work:

1. Ulid TenantId { get; }: This property contract mandates that any class implementing ITenanted must provide a TenantId getter. This is the key piece of information needed for filtering.
2. [EntityFilter<ITenanted>(nameof(Filter))]: This is the connection to our composable filter architecture. We are associating the ITenanted interface with a specific filter-providing method named Filter.
3. private static IQueryable<T> Filter(...): This method contains the filtering logic itself. It receives the current queryable (q) and the database context (ctx). It then appends a standard LINQ Where clause, comparing the entity's TenantId with the TenantId we stored in the context's Attributes.

When the DAL is initialized, our MappingSchema.ApplyEntityFilters<DbCtx>() helper will discover this attribute and automatically apply this Where clause to every query against any entity that implements ITenanted.

3. Automating Interface Scaffolding

To maintain our database-first philosophy, we must ensure that our generated entity classes automatically implement the ITenanted interface if their corresponding database tables contain a tenant_id column. This task is perfectly suited for our custom scaffolding interceptor.

We will add a simple check to the interceptor's logic.

File: DAL.ScaffoldInterceptor/Interceptor.cs (Addition)

// ITenanted
var tenantIdField = entityModel.Columns.FirstOrDefault(x =>
	x.Property.Name == nameof(ITenanted.TenantId)
);
if (tenantIdField is not null)
{
	addedInterfaces.Add(typeof(ITenanted));
}

This code snippet inspects the columns of the entity being scaffolded. If it finds a column that maps to a property named TenantId, it adds ITenanted to the list of interfaces for the generated partial class. With this change, entities like user which have a direct tenant_id column will be automatically protected by our tenancy filter without any manual intervention.

4. The Advanced Case: Projected Tenancy

The true test of our architecture is not in handling direct foreign keys, but in its ability to manage more complex, indirect relationships. Consider the post table. Its schema does not contain a tenant_id column. However, a post is unequivocally owned by a tenant because it belongs to a user, and that user belongs to a tenant. The Post entity's tenancy is therefore projected from its parent User entity.

Our DAL must enforce this projected relationship as a filtering rule. We need to make the Post entity implement ITenanted, but how do we implement the TenantId property when there is no backing column?

The solution lies in creating an expression-based property that Linq2Db can translate directly into SQL. We achieve this by manually creating a partial class for Post and using the [ExpressionMethod] attribute.

File: DAL.Context/Entity/Post.cs

namespace DAL.Context.Entity;

public partial class Post : ITenanted
{
	[ExpressionMethod(nameof(GetTenantIdExpression))]
	public Ulid TenantId => GetTenantIdExpression().Compile()(this);

	private static Expression<Func<Post, Ulid>> GetTenantIdExpression()
		=> x => x.User.TenantId;
}

Let's dissect this implementation, as it is central to the power of our DAL:

1. public partial class Post : ITenanted: We explicitly declare that Post conforms to the ITenanted contract. We do this manually because our scaffolder did not find a TenantId column.
2. private static Expression<Func<Post, Ulid>> GetTenantIdExpression(): Instead of a simple value, we define the logic for retrieving the TenantId as an expression tree. The expression x => x.User.TenantId is not just executable C# code, but it is a data structure that represents the navigation from a Post (x), through its User property, to that user's TenantId. Linq2Db is designed to parse these expression trees and convert them into the corresponding SQL, which in this case involves an INNER JOIN to the user table.
3. [ExpressionMethod(nameof(GetTenantIdExpression))]: This Linq2Db attribute is the magic that connects the property to the expression tree. When Linq2Db encounters post.TenantId inside a LINQ query (like the one in our ITenanted.Filter method), this attribute instructs it to substitute the SQL translation of the GetTenantIdExpression method's return value.
4. public Ulid TenantId => GetTenantIdExpression().Compile()(this);: This line handles the case where the TenantId property is accessed on an already-materialized Post object in C# memory, outside of a database query. In this scenario, Linq2Db is not involved, and the [ExpressionMethod] attribute has no effect. We must provide a valid C# implementation. We take our expression tree, .Compile() it into a runnable delegate, and then invoke it for the current Post instance (this). For performance-critical code, this compiled delegate could be cached, but we have kept it simple for clarity.

This elegant solution allows the TenantId property to work seamlessly both within database queries (translating to SQL) and on in-memory objects (executing as C# code).

5. Observing Composable Filters in Action

We have now implemented all the necessary components. The ITenanted interface defines the filter, the scaffolder applies it to entities with a direct TenantId, and we have manually implemented it for the Post entity using a projected property.

Let's verify that it all works together. We add a simple query to our application's entry point and inspect the generated SQL.

File: App/Program.cs (Addition)

// Get posts that are tenanted through a user
await ctx.GetTable<Post>()
    .ToListAsync();

Console.WriteLine(ctx.LastQuery);

Executing this code produces the following SQL:

SELECT
    [x].[id],
    [x].[user_id],
    [x].[title],
    [x].[content],
    [x].[created_at],
    [x].[modified_at],
    [x].[removed_at]
FROM
    [post] [x]
	INNER JOIN [user] [a_User] ON [x].[user_id] = [a_User].[id]
WHERE
    [a_User].[removed_at] IS NULL
    AND [a_User].[tenant_id] = X'0199DD7C56CCCB4521596E010C8BED67'
    AND [x].[removed_at] IS NULL

This output is a perfect confirmation of our architecture's capabilities. A simple call to ToListAsync() on the Post table has triggered a cascade of automated, composed filters:

• INNER JOIN [user] [a_User] ...:
  Linq2Db generated this join automatically because the ITenanted filter on Post required access to User.TenantId via our ExpressionMethod.
• WHERE ... [a_User].[tenant_id] = ...:
  This is our new tenancy filter in action, correctly applied to the joined user table.
• WHERE ... [x].[removed_at] IS NULL:
  The soft-delete filter for the Post entity is present, as expected from our previous work.
• WHERE ... [a_User].[removed_at] IS NULL:
  The soft-delete filter for the User entity has also been applied to the joined table. Our system correctly aggregates filters for all entities involved in the query.

This demonstrates the power of composable filters. Each rule (soft-delete, tenancy) is defined independently, yet the system correctly combines them into a single, comprehensive WHERE clause, ensuring data integrity and security automatically.

Conclusion

In this article, we successfully implemented automated, non-bypassable multi-tenancy filtering by building upon our existing architectural foundation. By defining tenancy as a behavioral interface and leveraging Linq2Db's ExpressionMethod, we were able to handle both direct and complex projected tenancy relationships with equal elegance. The final generated SQL proves that our composable filter system is robust, correctly layering multi-tenancy rules on top of soft-delete rules for all entities involved in a query.

This approach significantly reduces the risk of data leakage and removes the burden of security filtering from the business logic developer, fulfilling a core requirement of a true enterprise DAL.

You can review the complete implementation for this stage of the project on GitHub: https://github.com/ByteAether/EnterpriseDal/tree/part5.

Moving forward, we will tackle an even more granular security challenge: a generic, row-based permission system. The goal is to create a mechanism where any entity can be protected, requiring users to have explicit permission for that specific row in the database. If a user lacks the required permission, the entity will be automatically and transparently filtered from all query results, abstracting this complex conditional logic away from the business layer.