Issue Configuring `EnableDynamicJson()` on Data Source with a Multi-Tenant API Leveraging Postgres Row-Level Security

[MichaelCharles]

We're encountering an issue with our multi-tenant API that uses PostgreSQL with row-level security and JSONB columns. We're trying to configure Npgsql to handle JSONB serialization while maintaining our multi-tenant setup, but we're running into problems with excessive IServiceProvider instances being created.

Environment

• .NET Core 8
• Entity Framework Core 8.0.7
• Npgsql 8.0.4
• PostgreSQL 16.3

Repository With Reproducible Issue

A repository where you can reproduce the issue can be found here: https://github.com/MichaelCharles/ExampleMultiTenancyAPIWithRowLevelSecurity

Current Setup

Our API uses a multi-tenant architecture with the following key features:

1. PostgreSQL database with row-level security
2. JWT-based tenant identification
3. JSONB columns for flexible data storage

We determine the user's tenant through middleware that reads a JWT token and sets the tenant in HttpContext.Items. In the DbContext, we read this tenant and use it to set the username in the connection string, which Postgres then uses for row-level security.

The connection string is derived as follows,

    private static string GetConnectionString(
        IConfiguration configuration,
        IHttpContextAccessor? httpContextAccessor
    )
    {
        var host = configuration.GetValue<string>("Database:Host");
        var database = configuration.GetValue<string>("Database:Name");
        string? user = configuration.GetValue<string>("Database:Username");
        string? password = configuration.GetValue<string>("Database:Password");

        var httpContext = httpContextAccessor?.HttpContext;

        if (httpContext != null && httpContext.Items.ContainsKey("TenantId"))
        {
            string? tenantId = httpContext.Items["TenantId"] as string;
            user = $"user{tenantId}";
        }

        return $"Host={host}; Database={database}; Username={user}; Password={password};";
    }

Since we have JSONB columns which are serialized into C# classes, we need to explicitly enable dynamic JSON. We're currently doing that globally like so,

NpgsqlConnection.GlobalTypeMapper.EnableDynamicJson();

But this results in a warning saying that global setting of EnableDynamicJson() has been deprecated since .NET 7.

The Problem

We need to explicitly configure JSONB serialization. The recommended approach is to use NpgsqlDataSourceBuilder, but this is causing issues with our multi-tenant setup. If I create the data source and set it during startup, we lose the ability to flexibly change the connection string per request to leverage row-level security. If I create the data source within our DbContext, after about 20 requests I'm hit with a warning that Entity Framework is managing too many instances of IServiceProvider.

Attempted Solutions

1. Per-Context Configuration

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var dataSourceBuilder = new NpgsqlDataSourceBuilder(GetConnectionString());
        dataSourceBuilder.EnableDynamicJson();

        optionsBuilder.UseNpgsql(dataSourceBuilder.Build());
    }

This causes the "More than twenty 'IServiceProvider' instances" error after multiple API calls.

2. Startup Configuration

        var config = services.BuildServiceProvider().GetRequiredService<IConfiguration>();
        var httpContextAccessor = services
            .BuildServiceProvider()
            .GetRequiredService<IHttpContextAccessor>();

        var dataSourceBuilder = new NpgsqlDataSourceBuilder(
            GetConnectionString(config, httpContextAccessor)
        );
        dataSourceBuilder.EnableDynamicJson();

        var dataSource = dataSourceBuilder.Build();

        services.AddDbContext<AppDbContext>(options =>
        {
            options.UseNpgsql(dataSource);
        });

Configuring NpgsqlDataSourceBuilder during app startup works but prevents dynamic tenant-specific connection strings, breaking row-level security. The connection string is set once during startup, when no HttpContext is available, and so rather than being set to the per-tenant database user, it gets set to the default user.

Question

I'm looking for a way to,

1. Use the recommended NpgsqlDataSourceBuilder approach
2. Allow setting a different connection string per request based upon the "TenantId" set in the HttpContext to allow for row-level security
3. Avoid creating excessive IServiceProvider instances

Is there a recommended approach to handle this specific setup (multi-tenancy with row-level security and JSONB columns) with the latest Npgsql and EF Core versions?