Ensuring Tenant Isolation

In multi-tenant applications, a major concern is ensuring that queries are isolated to the correct customer or tenant -- with minimal and fail-safe effort. This feature is not built into Dapper.QX. Since there are so many ways to do it, and because I do make a few assumptions in my approach below. I thought it better to offer a suggested approach rather than trying to make it a supported feature. In a nutshell you:

1. Have an interface that you apply to your own TestableQuery instances, such as ITenantIsolated. This defines a query parameter that a tenant-isolated query in your application should have:

Example

public interface ITenantIsolatedQuery
{
    int CustomerId { get; set; }
}

In this example I have a CustomerId property, but it could be any such property appropriate to your application. It should have both a getter and setter. The interface name doesn't matter, either -- all we need to do is convey that the query is expected always to run for a single tenant only.

2. Apply this interface on your TestableQuery classes.

Example

public class MySampleQuery : TestableQuery<MySampleResult>, ITenantIsolatedQuery
{
    public MySampleQuery() : base("SELECT * FROM [dbo].[Something] WHERE [CustomerId] = @customerId ORDER BY [Whatever]")

    public int CustomerId { get; set; }

    public override IEnumerable<ITestableQuery> GetTestCasesInner()
    {
        yield return new MySampleQuery() { CustomerId = 1 };
    }
}

By using ITenantIsolatedQuery, we're forced to have a CustomerId property/parameter. You're not forced to apply it in the SQL, so you have to take some care there. The parameter should be required in the SQL and not part of a {where} or {andWhere} token. Notice how there's no [Where] or [Case] attribute on the CustomerId property. See Using query properties.

4. Invoke your queries through a service in your application rather than on their own.

Example

Instead of doing this throughout your code:

var results = await new MySanmpleQuery() { CustomerId = customerId }.ExecuteAsync(cn);

Do this:

@inject DataService Data

var results = await Data.QueryAsync(new MySampleQuery());

Note that we don't set the CustomerId property explicitly. This is the key to making your tenant-isolated queries fail-safe. You should not have to remember to set the CustomerId -- it should happen within your data service. Note of course there are different ways "inject" services. The example above is based on a fictional Blazor example.

5. Have a data service class that executes your queries. This is where your tenant-aware query properties are set. I'm assuming that you're using ASP.NET IdentityUser along with its typical ApplicationUser class created in the normal ASP.NET template. I'm assuming also that your ApplicationUser class has a CustomerId property that you added via EF migration to the dbo.AspNetUsers table.

Example

public class DataService
{
    private readonly string _connectionString;

    public DataService(string connectionString)
    {
        _connectionString = connectionString;
    }

    public ApplicationUser CurrentUser { get; private set; }

    public async Task SetCurrentUserAsync(string userName)
    {
        CurrentUser = await cn.QuerySingleOrDefaultAsync<ApplicationUser>(
            @"SELECT * FROM dbo.AspNetUsers WHERE [UserName]=@userName", 
            new { userName });
    }

    public async Task<IEnumerable<TResult>> QueryAsync<TResult>(Query<TResult> query)
    {
        if (query is ITenantIsolatedQuery tenantIsolated)
        {
            tenantIsolated.CustomerId = CurrentUser.CustomerId;
        }

        return await query.ExecuteAsync(GetConnection);
    }

    public IDbConnection GetConnection() => new SqlConnection(_connectionString); 
}

If you use your DataService consistently and apply your ITenantIsolated interface consistently throughout your application, you won't have to remember to set the CustomerId. The only thing left to do is make sure LoadCurrentUserAsync is called in your application.

6. Ensure that your DataService identifies the current user.

Blazor Example

In a .NET8 project, this would go in MainLayout.razor. I've omitted the markup for clarity and included just the @code section.

@inherits LayoutComponentBase
@inject DataService Data

@code {
    [CascadingParameter]
    private Task<AuthenticationState>? AuthenticationState { get; set; }

    protected override async Task OnInitializedAsync()
    {
        if (AuthenticationState is not null)
        {
            var authState = await AuthenticationState;
            if (authState.User.Identity?.IsAuthenticated ?? false)
            {
                await Data.LoadUserAsync(authState.User.Identity.Name);
            }			
        }
     }
}

Razor Page Example

Note, I didn't test this explicitly, but I think you will follow the logic, assuming .NET8, where I'm using primary constructors.

public class SamplePageModel(DataService dataServivce) : PageModel
{
    private readonly DataService Data = dataService;

    public async Task OnGetAsync()
    {
        if (HttpContext.User.Identity?.IsAuthenticated ?? false)
        {
            await Data.LoadUserAsync(HttpContext.User.Identity.Name!);
        }
    }
}