Hi, I need to use two different dbcontext at same request to fill out a dto. One dbcontext use the default Abp database where I have Host and all Tenants. The other dbcontext needs read and write a second database in order to collect additional data but in the same client request. In addition, every tenant has his own second database and so the connection string changes at runtime. None of the solutions I've tried works.
When the second context is created, the ExistingConnection is not null but I can't change it and I can't open a new one one the same transaction.
How can I achieve my requirement ?
public override void PreInitialize()
{
// Default Context on Abp Database
Configuration.Modules.AbpEfCore().AddDbContext<AdtContext>(options =>
{
if (options.ExistingConnection != null)
{
AdtConfigurer.Configure(options.DbContextOptions, options.ExistingConnection);
}
else
{
AdtConfigurer.Configure(options.DbContextOptions, options.ConnectionString);
}
});
// Second Db Context on different database that change for every Tenant
Configuration.Modules.AbpEfCore().AddDbContext<AdtLegacyContext>(options =>
{
if (options.ExistingConnection != null)
{
AdtLegacyConfigurer.Configure(options.DbContextOptions, options.ExistingConnection);
}
else
{
AdtLegacyConfigurer.Configure(options.DbContextOptions, options.ConnectionString);
}
});
}
The configurers:
public class AdtConfigurer
{
private const string MigrationTableName = "__AdtMigrationsHistory";
private const string MigrationTableSchema = "adt";
//https://github.com/aspnet/EntityFrameworkCore/issues/2180
public static void Configure(DbContextOptionsBuilder<AdtContext> builder, string connectionString)
{
builder.UseSqlServer(connectionString, o =>
o.MigrationsHistoryTable(MigrationTableName, MigrationTableSchema)
.UseRowNumberForPaging());
}
public static void Configure(DbContextOptionsBuilder<AdtContext> builder, DbConnection connection)
{
builder.UseSqlServer(connection, o =>
o.MigrationsHistoryTable(MigrationTableName, MigrationTableSchema)
.UseRowNumberForPaging());
}
}
public class AdtLegacyConfigurer
{
private const string MigrationTableName = "__AdtMigrationsHistory";
private const string MigrationTableSchema = "adt";
//https://github.com/aspnet/EntityFrameworkCore/issues/2180
//Quando l'issue viene risolta si può togliere UseRowNumberForPaging
public static void Configure(DbContextOptionsBuilder<AdtLegacyContext> builder, string connectionString)
{
builder.UseSqlServer(connectionString, o =>
o.MigrationsHistoryTable(MigrationTableName, MigrationTableSchema)
.UseRowNumberForPaging());
}
public static void Configure(DbContextOptionsBuilder<AdtLegacyContext> builder, DbConnection connection)
{
builder.UseSqlServer(connection, o =>
o.MigrationsHistoryTable(MigrationTableName, MigrationTableSchema)
.UseRowNumberForPaging());
}
}
The Default Context (All dataset omitted for brevity)
public class AdtContext: AbpDbContext
{
public DbSet<AuditData> Audits { get; set; }
public AdtContext(DbContextOptions options) : base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.ApplyConfiguration(new AuditConfiguration());
}
}
The second context (All dataset omitted for brevity):
public class AdtLegacyContext : AbpDbContext
{
private readonly ILegacyConnectionStringService _legacyConnectionStrings;
public AdtLegacyContext(DbContextOptions options, ILegacyConnectionStringService legacyConnectionStrings) : base(options)
{
_legacyConnectionStrings = legacyConnectionStrings;
}
public DbSet<TrcAnagraficaData> TrcAnagrafiche { get; set; }
.......
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var tenantId = AbpSession.GetTenantId();
var connectionString = _legacyConnectionStrings.GetConnectionString(tenantId); // <-- This resolve the connection string for tenant
optionsBuilder.UseSqlServer(connectionString);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.ApplyConfiguration(new TrcAnagraficaConfiguration());
......
}
}
Thank you.
19 Answer(s)
-
0
Are you doing this:
Single Deployment - Multiple Database
ln this approach, we run a single instance of the application on a server. We have a master (host) database to store tenant metadata (like tenant name and subdomain) and a separate database for each tenant. Once we identify the current tenant (for example; from subdomain or from a user login form), then we can switch to that tenant's database to perform operations.
In this approach, the application should be designed as multi-tenant at some level, but most of the application can remain independent from it.
We create and maintain a separate database for each tenant, this includes database migrations. If we have many customers with dedicated databases, it may take a long time to migrate the database schema during an application update. Since we have a separated database for each tenant, we can backup its database separately from other tenants. We can also move the tenant database to a stronger server if that tenant needs it.
This is what we do, then you can just use the standard AspNetZero repos to get the data out.
-
0
@ivanosw1 do you have an error message about this problem ?
-
0
Yes, many attempts produce different error, but the main is this:
ERROR 2018-03-12 08:55:44,332 [3 ] Mvc.ExceptionHandling.AbpExceptionFilter - Both an existing DbConnection and a connection string have been configured. When an existing DbConnection is used the connection string must be set on that connection. System.InvalidOperationException: Both an existing DbConnection and a connection string have been configured. When an existing DbConnection is used the connection string must be set on that connection. at Microsoft.EntityFrameworkCore.Storage.RelationalConnection..ctor(RelationalConnectionDependencies dependencies)
If I try to change the connection on the existing Dbconnection the error is:
ERROR 2018-03-12 09:25:43,181 [9 ] Mvc.ExceptionHandling.AbpExceptionFilter - Not allowed to change the 'ConnectionString' property. The connection's current state is open. System.InvalidOperationException: Not allowed to change the 'ConnectionString' property. The connection's current state is open. at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key) at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
For @BBakerMMC : What you say is exactly what I would like to obtain.
-
0
Context:
using Abp.EntityFrameworkCore; using Microsoft.EntityFrameworkCore; using MMC.Platform.DMPModels; namespace MMC.Platform.EntityFrameworkCore { public class DMPContext : AbpDbContext { //EX public DbSet<CompanyAddress> CompanyAddress { get; set; } public DbSet<CompanyAddressType> CompanyAddressType { get; set; } public DbSet<CompanyPhone> CompanyPhone { get; set; } public DbSet<CompanyEmail> CompanyEmail { get; set; } public DbSet<CompanyIdentifier> CompanyIdentifier { get; set; } public DbSet<CompanyNote> CompanyNote { get; set; } public DMPContext(DbContextOptions<DMPContext> options) : base(options) { } } }configurer:
using System.Data.Common; using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.Configuration; namespace MMC.Platform.EntityFrameworkCore { public static class DMPContextConfigurer { public static void Configure(DbContextOptionsBuilder<DMPContext> builder, string connectionString) { builder.UseSqlServer(connectionString, o => o.UseRowNumberForPaging()); } public static void Configure(DbContextOptionsBuilder<DMPContext> builder, DbConnection connection) { builder.UseSqlServer(connection, o => o.UseRowNumberForPaging()); } } }Factory:
using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Design; using Microsoft.EntityFrameworkCore.Infrastructure; using Microsoft.Extensions.Configuration; using MMC.Platform.Configuration; using MMC.Platform.Web; namespace MMC.Platform.EntityFrameworkCore { /* This class is needed to run "dotnet ef ..." commands from command line on development. Not used anywhere else */ public class DMPContextFactory : IDesignTimeDbContextFactory<DMPContext> { public DMPContext CreateDbContext(string[] args) { var builder = new DbContextOptionsBuilder<DMPContext>(); var configuration = AppConfigurations.Get(WebContentDirectoryFinder.CalculateContentRootFolder()); DMPContextConfigurer.Configure(builder, configuration.GetConnectionString(PlatformConsts.ConnectionStringName)); return new DMPContext(builder.Options); } } }In EF * CoreModules Add:
Configuration.Modules.AbpEfCore().AddDbContext<DMPContext>(options => { //DMPContextConfigurer.Configure(configuration.DbContextOptions, configuration.ConnectionString); if (options.ExistingConnection != null) { DMPContextConfigurer.Configure(options.DbContextOptions, options.ExistingConnection); } else { DMPContextConfigurer.Configure(options.DbContextOptions, options.ConnectionString); } });Usage
private readonly IRepository<Company> _repositoryCompany; -
0
Sorry but I don't see any difference from my code. Note the I need to access in the same request to both repository:
public async Task<DatiSatinariDto> GetAsync(int id) { var logs = await _auditDataRepository.GetAsync(20136); // <-- DEFAULT DATABASE var datiSanitari = await _datiSantitariRepository.GetAsync(id); // <-- EXTERNAL DATABASE var result = datiSanitari.MapTo<DatiSatinariDto>(); return result; }The errore message is the same:
ERROR 2018-03-12 17:43:33,830 [17 ] Mvc.ExceptionHandling.AbpExceptionFilter - Both an existing DbConnection and a connection string have been configured. When an existing DbConnection is used the connection string must be set on that connection. System.InvalidOperationException: Both an existing DbConnection and a connection string have been configured. When an existing DbConnection is used the connection string must be set on that connection. at Microsoft.EntityFrameworkCore.Storage.RelationalConnection..ctor(RelationalConnectionDependencies dependencies)
-
0
Other suggestions ? I'm sure that is possibile but maybe I'm missing some concept.
-
0
@ivanosw1 I think you need to change the constructor of your AdtContext like below; You also need to use same approach for your second dbContext.
public AdtContext(DbContextOptions<AdtContext> options) : base(options) { } -
0
Sure @ismcagdas . I have added on my two db context the specified type, but the error is still the same.
public AdtLegacyContext(DbContextOptions<AdtLegacyContext> options, ILegacyConnectionStringService legacyConnectionStrings) : base(options) { _legacyConnectionStrings = legacyConnectionStrings; } public AdtContext(DbContextOptions<AdtContext> options) : base(options) { }ERROR 2018-03-14 16:15:03,624 [8 ] Mvc.ExceptionHandling.AbpExceptionFilter - Both an existing DbConnection and a connection string have been configured. When an existing DbConnection is used the connection string must be set on that connection. System.InvalidOperationException: Both an existing DbConnection and a connection string have been configured. When an existing DbConnection is used the connection string must be set on that connection. at Microsoft.EntityFrameworkCore.Storage.RelationalConnection..ctor(RelationalConnectionDependencies dependencies)
-
0
Some progress. I think that the real problem wasn't focused.
In order to access to my external legacy database, I need to change at runtime the connection string. Exactly as you do when the Tenant as separate db.
So.... I guess the problems is here. Maybe that the solution is to implement a different ConnectionStringResolver ?
If so, what is the best approach to extends the default resolver and distinguish from default AbpContext and all the other legacy db context (this are more than one defined in many modules).
Thanks
-
0
Hi,
Sorry for the late response. Can you explain what ILegacyConnectionStringService interface does ?
If you can share the full source code for AdtLegacyContext, we can take a deeper look. You can explude your entities if you would like to.
-
0
Hi, We have a legacy context that needs to access to an external database. Default abp context is used as usual. Legacy context has a different connectionstring for every tenant. ConnectionStrings are retrived by a ILegacyConnectionStringService that reads from file. Look at GetNameOrConnectionString: is this the rigth way?
//Simple legacy context public class AdtLegacyContext : AbpDbContext { public DbSet<TrcAnagraficaData> TrcAnagrafiche { get; set; } public AdtLegacyContext(DbContextOptions<AdtLegacyContext> options) : base(options) { } }public class AdtLegacyRepositoryModule : AbpModule { public override void Initialize() { IocManager.RegisterAssemblyByConvention(typeof(AdtLegacyRepositoryModule).GetAssembly()); } public override void PreInitialize() { //Replace the connectionString resolver service Configuration.ReplaceService<IConnectionStringResolver, LegacyConnectionStringResolver>(Abp.Dependency.DependencyLifeStyle.Transient); //Abp context Configuration.Modules.AbpEfCore().AddDbContext<DefaultContext>(options => { if (options.ExistingConnection != null) { DefaultContextConfigurer.Configure(options.DbContextOptions, options.ExistingConnection); } else { DefaultContextConfigurer.Configure(options.DbContextOptions, options.ConnectionString); } }); //LegacyContext Configuration.Modules.AbpEfCore().AddDbContext<AdtLegacyContext>(options => { if (options.ExistingConnection != null) { AdtLegacyConfigurer.Configure(options.DbContextOptions, options.ExistingConnection); } else { AdtLegacyConfigurer.Configure(options.DbContextOptions, options.ConnectionString); } }); } }public class LegacyConnectionStringResolver : DbPerTenantConnectionStringResolver { private readonly ILegacyConnectionStringService _legacyConnectionStrings; public LegacyConnectionStringResolver(ILegacyConnectionStringService legacyConnectionStrings, IAbpStartupConfiguration configuration, ICurrentUnitOfWorkProvider currentUnitOfWorkProvider, ITenantCache tenantCache) : base(configuration, currentUnitOfWorkProvider, tenantCache) { _legacyConnectionStrings = legacyConnectionStrings; } //Is this the right way? public override string GetNameOrConnectionString(ConnectionStringResolveArgs args) { if (args["DbContextType"] == typeof(AdtLegacyContext)) { var tenantId = GetCurrentTenantId(); if (!tenantId.HasValue) { throw new InvalidOperationException("Legacy db context must have tenant"); } var connectionString = _legacyConnectionStrings.GetConnectionString(tenantId.Value);//Read from file return connectionString; } return base.GetNameOrConnectionString(args); } } -
0
Hi,
Sorry for my late response. I have created a demo for using multiple dbContext in Entity Framework Core. You can check it here <a class="postlink" href="https://github.com/aspnetboilerplate/aspnetboilerplate-samples/tree/master/MultipleDbContextEfCoreDemo">https://github.com/aspnetboilerplate/as ... EfCoreDemo</a>.
The main thing is MyConnectionStringResolver class which decides the connection string for DbContexts. You can apply a similar ConnectionStringResolver in your case.
I hope this helps.
-
0
Thank you @ismcagdas.
I've update my code according to your and now all works good.
The only difference is that I've a BaseClass to extends in order to use in other LegacyContext, and the string resolver extends DbPerTenantConnectionStringResolver instead of DefaultConnectionStringResolver.
So the issue is closed.
-
0
@ivanosw1 I'm glad that it worked for you :).
-
0
I am not able to compile sample project. See issue: <a class="postlink" href="https://github.com/aspnetboilerplate/aspnetboilerplate-samples/issues/57">https://github.com/aspnetboilerplate/as ... /issues/57</a>
-
0
Is there doc for the steps to setup multiple database or separate one for a specific entity?
For example, its not clear how entity knows which database it is stored in?
Thanks.
-
0
@vladsd replied on the related issue about build problem.
Is there doc for the steps to setup multiple database or separate one for a specific entity?
No.
For example, its not clear how entity knows which database it is stored in?
If you add one entity to a single DbContext, ABP will find it automatically.
-
0
Hi,
while downloading from below link, i got error states that
https://github.com/aspnetboilerplate/as ... EfCoreDemo.
"MultipleDbContextEfCoreDemo.EntityFrameworkCore(load failed)"
Unable to find project information for 'E:\aspnetboilerplate-samples-master (1)\aspnetboilerplate-samples-master\MultipleDbContextEfCoreDemo\src\MultipleDbContextEfCoreDemo.EntityFrameworkCore\MultipleDbContextEfCoreDemo.EntityFrameworkCore.csproj'. Inside Visual Studio, this may be because the project is unloaded or not part of current solution. Otherwise the project file may be invalid or missing targets required for restore.
Could you please resolve this issue.
-
0
@ismcagdas and @ivanosw1 i m also working on multiple db context . i see the demo project . but i don't know how change connectionstrong dynamically. @ivansow1 change connection string per tenant at run time like you are doing.how can i achieve this .
