Skip to main content

Data Isolation

PearDrop isolates tenant data at the logical level using a Shared Database model. All tenants share the same database, schema, and tables, with data isolation enforced using a TenantId column on every entity.

Architecture​

All tenants share a single SQL Server database. Each table includes a TenantId foreign key that identifies which tenant owns each row.

CREATE TABLE dbo.Orders (
    Id uniqueidentifier PRIMARY KEY,
    TenantId uniqueidentifier NOT NULL,  -- Tenant identifier
    OrderNumber nvarchar(50) NOT NULL,
    CustomerName nvarchar(200) NOT NULL,
    OrderDate datetime2 NOT NULL,
    Total decimal(18,2) NOT NULL,
    
    CONSTRAINT FK_Orders_Tenants FOREIGN KEY (TenantId) 
        REFERENCES dbo.Tenants(Id),
    INDEX IX_Orders_TenantId (TenantId)
);

CREATE TABLE dbo.Tenants (
    Id uniqueidentifier PRIMARY KEY,
    Identifier nvarchar(50) NOT NULL UNIQUE,
    Name nvarchar(200) NOT NULL,
    IsEnabled bit NOT NULL
);

Automatic Query Filtering​

PearDrop automatically filters all queries by the current tenant using standardized configuration. You don't need to manually add WHERE TenantId = ... in your code.

DbContext Configuration​

public class MyAppReadDbContext : PearDropReadDbContextBase<MyAppReadDbContext>
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Pass getFilterData to entity configurations
        // Configurations extending TenantFilteredEntityTypeConfigurationBase
        // automatically apply tenant filters
        modelBuilder.ApplyConfiguration(
            new OrderProjectionTypeConfiguration(this.GetFilterData));
        modelBuilder.ApplyConfiguration(
            new CustomerProjectionTypeConfiguration(this.GetFilterData));
    }
}

// Entity configuration with standardized filtering
public sealed class OrderProjectionTypeConfiguration : 
    TenantFilteredEntityTypeConfigurationBase<OrderProjection>
{
    public OrderProjectionTypeConfiguration(Func<string, object?> getFilterData)
        : base(getFilterData)
    {
    }
    
    protected override void ConfigureEntity(EntityTypeBuilder<OrderProjection> builder)
    {
        builder.ToView("vw_order");
        builder.HasKey(o => o.Id);
    }
    
    protected override Expression<Func<OrderProjection, Guid>> GetTenantIdExpression()
        => order => order.TenantId;
}
Standardized Pattern

PearDrop uses TenantFilteredEntityTypeConfigurationBase<T> for consistent tenant filtering across all modules. This eliminates manual HasQueryFilter() calls and ensures consistent isolation logic.

See Advanced Multi-Tenancy Patterns for complete details.

Legacy Manual Configuration​

Older code may use manual filter application in DbContext:

// ❌ Legacy pattern - avoid for new code
public class MyAppDbContext : PearDropDbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        
        // Manual filters - not recommended
        var tenantId = this.GetFilterData<Guid>("tenantId");
        modelBuilder.Entity<Order>()
            .HasQueryFilter(o => o.TenantId == tenantId);
    }
}

For new code, use the standardized base class approach shown above.

Automatic Filtering in Queries​

// Your code
var orders = await readModels.Orders.ToListAsync();

// EF Core generates (with tenant filter applied automatically)
// SELECT * FROM Orders WHERE TenantId = @CurrentTenantId

No need to explicitly add tenant filters—PearDrop handles it automatically.

Admin Operations​

For admin operations that need to see all tenant data, use IgnoreQueryFilters():

public async Task<AdminStatsResult> GetStatisticsAcrossAllTenants(
    CancellationToken cancellationToken)
{
    var stats = await dbContext.Orders
        .IgnoreQueryFilters()  // Bypass tenant filter for admin view
        .GroupBy(o => o.TenantId)
        .Select(g => new TenantStatistics
        {
            TenantId = g.Key,
            OrderCount = g.Count(),
            TotalRevenue = g.Sum(o => o.Total)
        })
        .ToListAsync(cancellationToken);
    
    return new AdminStatsResult(stats);
}

Use IgnoreQueryFilters() carefully - only in authorized admin endpoints backed by proper authorization checks.

Advantages​

  • Simple: One database, one connection string, no complexity
  • Cost-effective: Shared infrastructure costs
  • Scales easily: Thousands of tenants in one database
  • Easy backups: Single backup covers all tenants
  • Simple migrations: Run once, applies to all tenants
  • Easy administration: Single SQL Server to manage

Considerations​

  • Lower physical isolation: All tenant data in same tables (but logically isolated via TenantId)
  • Noisy neighbor effect: One tenant's heavy queries can affect performance for others
  • Compliance: Some regulatory frameworks prefer physical data separation (though logical isolation is generally sufficient)

Best For​

  • SaaS applications with many tenants
  • B2B applications with moderate to small tenant counts
  • Cost-sensitive deployments
  • Applications that can use logical isolation for compliance

Next Steps​