Make NpgsqlDataSource a scoped service instead of singleton

[roji]

NpgsqlDataSource is currently registered as a singleton service; although this conceptually makes sense, it creates various issues:

• When automatically getting the data source from DI, there's no way in EF to include the data source instance in the decision-making on whether a new service provider is needed, causing interference between service providers (Using NpgsqlDataSource in DependencyInjection pollutes other containers #2891).
• In some multi-tenant and testing scenarios, it's desirable to use many data sources with potentially the same EF service provider. In the current situation, this requires lots of EF service providers, which triggers our "many service providers" warning.

The main thing blocking making NpgsqlDataSource scoped is the fact that we get enum mappings from it in NpgsqlTypeMappingSource, which is itself a singleton. We should stop doing that (#3063), at which point it should be easy to make NpgsqlDataSource scoped.

[gbd3-en]

Excellent! We're in that multi-tenant scenario. Despite careful re-use & disposal of the NpgsqlDataSoruce and DbContext instances, we still end up with that ManyServiceProvidersCreatedWarning error.

Here is the full error in case it helps others searching for solutions:

'Microsoft.EntityFrameworkCore.Infrastructure.ManyServiceProvidersCreatedWarning':
More than twenty 'IServiceProvider' instances have been created for internal use by Entity Framework. This is commonly caused by injection of a new singleton service instance into every DbContext instance.

[adnan-kamili]

@roji I see this issue as closed but we are still facing this issue in the efcore 9.0

System.InvalidOperationException: An error was generated for warning 'Microsoft.EntityFrameworkCore.Infrastructure.ManyServiceProvidersCreatedWarning': More than twenty 'IServiceProvider' instances have been created for internal use by Entity Framework. This is commonly caused by injection of a new singleton service instance into every DbContext instance.

We have ensured that in our multi-tenant system, NpgsqlDataSource is cached so each tenant has exactly one NpgsqlDataSource. Here is our code:

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseNpgsql(NpgsqlConfiguration.GetNpgsqlDataSource((GetDatabaseConnectionString())), NpgsqlConfiguration.GetNpgsqlBuilderOptions());
        }
    }

Here is the NpgsqlConfiguration:

public static class NpgsqlConfiguration
{
    private static readonly Action<NpgsqlDbContextOptionsBuilder> _builderOptions = builderOptions =>
    {
        builderOptions.UseNodaTime().MapEnum<ViewPermission>();
    };

    [Cache(ProfileName = PostSharpCachingProfile.ImmutableCache)]
    public static NpgsqlDataSource GetNpgsqlDataSource(string connectionString)
    {
            var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
            // Logger for Npgsql
            dataSourceBuilder.UseLoggerFactory(Logging.GetLoggerFactory());
            dataSourceBuilder.UseNodaTime();
            dataSourceBuilder.MapEnum<ViewPermission>();
            return dataSourceBuilder.Build();
    }

    // Note that when using an external NpgsqlDataSource, you need to configure mappings and plugins 
    // at both levels yourself - both on the NpgsqlDataSource and at the EF provider level.
    public static Action<NpgsqlDbContextOptionsBuilder> GetNpgsqlBuilderOptions()
    {
        return _builderOptions;
    }
}

Any guidance you can provide?

[roji]

OnConfiguring is executed for every DbContext instance, so GetNpgsqlDataSource gets called multiple times, and constructs a new data source each time. That's not what you want.

The recommendation since 9.0 is to use ConfigureDataSource rather than manage constructing the data source yourself and provide it (see 9.0 release notes). Otherwise, if you want to continue an external NpgsqlDataSource, construct it once outside and pass the same instance in.

[adnan-kamili]

Thanks, I missed that, I was still using the old method of configuring by creating datasources, just passing in the connection string and build options solved the issue.