I have a scenario where I need to read/write from the primary database for normal operations but for some operations I need to read from an alternate server (a replication target that's read only that we use for reporting). With the way the new Core API

Question

public class ReadOnlyContext : BaseDbContext
{
public ReadOnlyContext(ITenantInfo currentTenant, DbContextOptions<ReadOnlyContext> options, ICurrentUser currentUser, ISerializerService serializer, IOptions<DatabaseSettings> dbSettings, IEventPublisher events, IServiceProvider serviceProvider, TenantDbContext tenanatDbContext)
 {
 _tenanatDbContext = tenanatDbContext;}
public class ApplicationDbContext : BaseDbContext
{
    private TenantDbContext _tenanatDbContext;
    public ApplicationDbContext(ITenantInfo currentTenant, DbContextOptions<ApplicationDbContext> options, ICurrentUser currentUser, ISerializerService serializer, IOptions<DatabaseSettings> dbSettings, IEventPublisher events, IServiceProvider serviceProvider, TenantDbContext tenanatDbContext)
        : base(currentTenant, options, currentUser, serializer, dbSettings, events, serviceProvider)
    {
        _tenanatDbContext = tenanatDbContext;
    }
    #region MasterData
    public DbSet<MasterAreaUnit> MasterAreaUnits => Set<MasterAreaUnit>();
}in 
ApplicationDbContext i am doing both operation write and read but in the ReadOnlyDbContext i need to read the data with the connection of the applicationDB context where read db context is connected with the readreplica

Answer 1

its not clear what your question is. the readonly dbcontext just needs its own connect string.

Answer 2

Hi, @Bharathi Hattikal. Welcome to Microsoft Q&A.

You could get the connectionString through GetConnectionString() and set the connectionString through SetConnectionString().

Reference code:

var connectionString = _applicationContext.Database.GetConnectionString();
_readOnlyContext.Database.SetConnectionString(connectionString);

Of course, you could set the connectionString of ReadOnlyContext just like setting ApplicationContext, without getting it from ApplicationContext.

Note: You could add ApplicationIntent=ReadOnly to the connection string of the ReadOnlyContext, but this does not prevent ReadOnlyContext from writing operations.

If you want to make the ReadOnlyContext read-only, you could refer to the following solutions: Solution 1: DbSet in ReadOnlyContext only keeps the get method.

public virtual DbSet<Student> Students { get; }

Solution 2: Override SaveChanges in ReadOnlyContext and let it throw an exception when called.

public override int SaveChanges()
{
    throw new InvalidOperationException("Cannot save changes in read-only context.");
}

public override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
{
    throw new InvalidOperationException("Cannot save changes in read-only context.");
}

Solution 3: Register an interceptor for ReadOnlyContext to throw an exception when ReadOnlyContext executes SaveChanges. Create interceptor

    public class ReadOnlyInterceptor : SaveChangesInterceptor
    {
        public override InterceptionResult<int> SavingChanges(DbContextEventData eventData, InterceptionResult<int> result)
        {
            throw new InvalidOperationException("Cannot save changes in read-only context.");
        }

        public override ValueTask<InterceptionResult<int>> SavingChangesAsync(DbContextEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default)
        {
            throw new InvalidOperationException("Cannot save changes in read-only context.");
        }
    }

Register interceptor

builder.Services.AddDbContext<ReadOnlyContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("SqlServer_ReadOnly"))
       .AddInterceptors(new ReadOnlyInterceptor()));

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

still i am getting the exception like where it was not taking the schema name

so i am getting the exception like relationship of the table not exsit it was not configure successfully
internal static class Startup

{

private static readonly ILogger _logger = Log.ForContext(typeof(Startup));

internal static IServiceCollection AddPersistence(this IServiceCollection services, IConfiguration config)

{

    services.AddOptions<DatabaseSettings>()

        .BindConfiguration(nameof(DatabaseSettings))

        .PostConfigure(databaseSettings =>

        {

            _logger.Information("Current DB Provider: {dbProvider}", databaseSettings.DBProvider);

        })

        .ValidateDataAnnotations()

        .ValidateOnStart();

    services.AddDbContext<ReadOnlyContext>((p, m) =>

    {

        var tenantInfo = services?.BuildServiceProvider()?.GetService<IMultiTenantContextAccessor<LrbTenantInfo>>();

        var connection = tenantInfo?.MultiTenantContext?.TenantInfo?.ConnectionString;

        var databaseSettings = p.GetRequiredService<IOptions<DatabaseSettings>>().Value;

        m.UseDatabase(databaseSettings.DBProvider, string.IsNullOrEmpty(connection) ? databaseSettings.ReadReplicaConnectionString : connection);

    });

public class ReadOnlyContext : BaseDbContext

{

    private TenantDbContext _tenanatDbContext;

    public ReadOnlyContext(ITenantInfo currentTenant, DbContextOptions<ReadOnlyContext> options, ICurrentUser currentUser, ISerializerService serializer, IOptions<DatabaseSettings> dbSettings, IEventPublisher events, IServiceProvider serviceProvider, TenantDbContext tenanatDbContext)

        : base(currentTenant, options, currentUser, serializer, dbSettings, events, serviceProvider)

    {

        _tenanatDbContext = tenanatDbContext;

    }

    

        

    #region DataManagement

    public DbSet<Prospect> Prospects => Set<Prospect>();

   

    #endregion

}  public partial class ProspectRepository : EFRepository<Prospect>, IProspectRepository

{

  private readonly IServiceProvider _provider;

  private readonly ILogger _logger;

  private readonly IDapperRepository _dapperRepository;

  private readonly ICurrentUser _currentUser;

  private readonly ReadOnlyContext _readOnlyContext;

  public ProspectRepository

      (

      ReadOnlyContext dbContext,

      ICurrentUser currentUser, ReadOnlyContext readOnlyContext) : base(dbContext)

  {



      _readOnlyContext = readOnlyContext;

  }

  public async Task<List<Prospect>> ListAsync()

  {

      var scope = _provider.CreateScope();

      var context = scope.ServiceProvider.GetRequiredService<ReadOnlyContext>();

      return await context.Prospects.Where(i => !i.IsDeleted).ToListAsync();

  }

here i am getting this exception some where i missed to configuration