How to correctly use 2 different DbContexts using Entity Framework Core to connect to postgres reader instance, writer instance

Question

I have 2 database (AWS Aurora Postgres) instances - reader instance and writer instance. There are 2 different connection strings for the instances and I want to utilize both instances.

I want to use reader instance to run DQL commands and writer instance run other commands.

I have a class called DatabaseContext which has the following code:

using Microsoft.EntityFrameworkCore;

namespace Com.Proj.Repository
{
    public class DatabaseContext : DbContext
    {
        public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options)
        {
        }

        public DbSet<Data> EngineData { get; set; }
        
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Data>(entity =>
            {
                entity.HasKey(k => k.Id);
                entity.Property(t => t.Id).HasColumnName("id");
                entity.Property(t => t.Data).HasColumnName("data");
                entity.Property(t => t.Time).HasColumnName("time");
            });
        }
    }
}

I created another file with same exact code but with a different class name ReadDatabaseContext and in dependency injection, I did:

services.AddDbContext<ReadDatabaseContext>(options => options.UseNpgsql(readDbConnection, options => options.EnableRetryOnFailure(3, TimeSpan.FromSeconds(2), null))
                .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

when I update something with my writer db context, I get this error:

instance of entity type cannot be tracked because another instance with same key value is tracked

I added this code in my ReadDatabaseContext:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}

I also added .AsNoTracking() wherever I am using the ReadDatabaseContext to read data.

But nothing worked.

What are the actual queries you are running when you get this error? If you happen to be reading entities with a Read DbContext and associating them to entities on the Write DbContext then the fact that Read is AsNoTracking won't be relevant to the problem, it will be how you are trying to pass references into the Write DbContext. — Steve Py
– Steve Py, Commented Jun 17, 2024 at 8:22
I am executing this line first- await readDbContext.EngineData.Where(or => or.Id == id).ToListAsync(); and then in a different method, I am executing this line which is where I am getting the exception- context.EngineData.Update(outstandingRequest); await context.SaveChangesAsync(); — Vivek
– Vivek, Commented Jun 19, 2024 at 6:53
If the outstandingRequest was read from the readDbContext then you're going to have issues if a matching record with the same Id is currently being tracked by the write DbContext (context). You need to first check if there is an instance being tracked and consider copying the values across in the case there is one, or detach it from the write DbContext if you want to do a carte blanch overwrite. If there are related entities within the EngineData (does not appear to as nothing was eager loaded) then those would need to be handled individually as well. I'll add an answer with details. — Steve Py
– Steve Py, Commented Jun 19, 2024 at 9:52

Steve Py · Accepted Answer · 2024-06-19 10:12:01Z

Continuing on the comment, if you are reading one or more EngineData instances from the read-only DbContext using code like:

await readDbContext.EngineData.Where(or => or.Id == id).ToListAsync();

... then passing those to a method that performs an update using:

context.EngineData.Update(outstandingRequest);

This can end up failing when the write-enabled context instance happens to already be tracking an instance with the same ID. This could be from an earlier operation or scenario where that record happened to be loaded/added/retrieved as part of a tracked query.

Your options are to either always retrieve and transfer values, or check for a tracked instance and either copy values across if found, reserving Update only if it isn't found, or detaching any tracked instance to do a force overwrite.

Option 1: Read and copy

public async Task UpdateEngine(EngineData updatedEngine)
{
    ArgumentNullException.ThrowIfNull(updatedEngine);

    var existingEngine = await context.EngineData.SingleAsync(or => or.Id == updatedEngine.Id);
    context.Entry(existingEngine).CurrentValues.SetValues(updatedEngine);
    await context.SaveChangesAsync();
}

The disadvantage of this approach is it always performs a read, though this ensures the latest data where you can check for things like concurrency tokens which would fail above. This also updates all values where you might only expect a few select values to actually be changed. It leaves a window open for bugs to slip in. Alternatively you could copy allowed values across manually or configure a mapper with rules to copy just the values you want to allow instead of using SetValues.

Option 2: Check before copying/Update:

public async Task UpdateEngine(EngineData updatedEngine)
{
    ArgumentNullException.ThrowIfNull(updatedEngine);

    var existingEngine = await context.EngineData.Local.FirstOrDefaultAsync(or => or.Id == updatedEngine.Id);
    if (existingEngine == null)
        context.Update(updatedValue);
    else
        context.Entry(existingEngine).CurrentValues.SetValues(updatedEngine);
    await context.SaveChangesAsync();
}

This is similar to Option 1, but only goes to the tracking cache to see if there is an instance already tracked. If we don't find an instance we call Update. If we do find an instance we copy the values across like option 1. The advantage is avoiding the extra read from DB.

Option 3:

public async Task UpdateEngine(EngineData updatedEngine)
{
    ArgumentNullException.ThrowIfNull(updatedEngine);

    var existingEngine = await context.EngineData.Local.FirstOrDefaultAsync(or => or.Id == updatedEngine.Id);
    if (existingEngine != null)
        context.Entry(existingEngine).State = EntityState.Detached;
    context.Update(updatedEngine);

    await context.SaveChangesAsync();
}

Like option 2 we check the tracking cache, except in this case if we find one, we remove it from the tracking cache by updating it's State to Detached. This removes the collision if one is already tracked so we can use Update to overwrite the data.

All options can end up triggering concurrency exceptions if you want to guard data updates /w optimistic concurrency timestamps or row versions, though with option 1 you would need to check on this manually, but have more control over what happens rather than reacting to an exception.

If you end up working with entities that have loaded related entities (one-to-many, many-to-one, etc.) then all options would needs steps added to address any changes needed to related entities whether modifying related rows, or adding/removing relations.

Collectives™ on Stack Overflow

How to correctly use 2 different DbContexts using Entity Framework Core to connect to postgres reader instance, writer instance

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related