DEV Community

Cover image for Building a Secure DAL: Composable Multi-Tenancy Filtering with C# and Linq2Db
GigAHerZ
GigAHerZ

Posted on • Originally published at byteaether.github.io

Building a Secure DAL: Composable Multi-Tenancy Filtering with C# and Linq2Db

#dotnet #security #architecture #csharp

Securing a multi-tenant application means enforcing strict data isolation. This cannot be left to business logic, but it must be guaranteed at the Data Access Layer (DAL). We built upon our existing enterprise DAL foundation to implement automated, non-bypassable multi-tenancy filtering.

This article summarizes the key architectural steps for senior engineers and architects looking to solve this critical cross-cutting concern.

The Problem and Our Goal

The goal is to ensure that every query executed against a tenant-aware entity is automatically filtered by the current tenant's ID. We need this filter to compose seamlessly with other existing global filters, such as soft-delete.

1. Passing Tenant Context to the DAL

Our DAL must be tenant-aware. We update our base database context interface, IDbCtx, to hold request-scoped attributes, starting with the TenantId.

public interface IDbCtx : IDataContext
{
    DbCtxAttributes Attributes { get; set; }
    public record DbCtxAttributes(Ulid TenantId = default);
}
Enter fullscreen mode Exit fullscreen mode

This attribute is initialized early in the request lifecycle, ensuring the current TenantId is available for the database context's lifetime.

2. Defining the Tenancy Contract

We use an ITenanted interface to define the behavior and attach the filtering logic. The [EntityFilter] attribute ties the interface to the static Filter method, which contains the LINQ Where clause.

[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);
}
Enter fullscreen mode Exit fullscreen mode

Our global filter system discovers this attribute and automatically applies this Where clause to every query against implementing entities.

3. Solving Projected Tenancy

Not all entities have a direct tenant_id column. A Post, for example, may inherit its tenancy from its parent User. This is "Projected Tenancy."

To handle this, we manually implement ITenanted for the Post entity and use the Linq2Db [ExpressionMethod] attribute. This instructs Linq2Db to translate the property access into a SQL expression involving a join.

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;
}
Enter fullscreen mode Exit fullscreen mode

Linq2Db parses the expression x => x.User.TenantId, generating an INNER JOIN to the user table and applying the tenant filter to the joined user's tenant_id column.

Conclusion: Composable Security

This architecture allows security rules to be defined once and enforced automatically. The generated SQL proves that soft-delete rules and multi-tenancy rules are correctly layered, guaranteeing both data integrity and isolation.

For the complete implementation, including scaffolding automation and the full generated SQL analysis, see the full article on my blog.

Top comments (0)