.net core - Simultaneous data operation in SQLite and SQL Server databases using Entity Framework and Repository Pattern

Question

I am working on a .net core project where the requirement is to maintain an SQLite DB and an SQL Server DB simultaneously. I created two DbContext files SqlServerContext and SqliteContext and separate migration folders for them. These files are derived from a WorkerContext file that's derived from DbContext. The migration is working properly, as tables are created in both databases. But I could not make simultaneous data operation work.

This is the IKeyboardMouseActivityRepository. There are separate parts for using SqliteContext and SqlServerContext. I have to comment out one part when using the other. So I can do data entry in one DB at a time now.

public interface IKeyboardMouseActivityRepository : 
    IRepository<KeyboardMouseActivity, Guid, SqlServerContext> 
//     IRepository<KeyboardMouseActivity, Guid, SqliteContext>
{ 
}

public class KeyboardMouseActivityRepository :  
    IKeyboardMouseActivityRepository,
    Repository<KeyboardMouseActivity, Guid, SqlServerContext>
//  Repository<KeyboardMouseActivity, Guid, SqliteContext>
{
    public KeyboardMouseActivityRepository(SqlServerContext dbContext)
        : base(dbContext)
    {
    }

    //  public KeyboardMouseActivityRepository(SqliteContext dbContext)
    //      : base(dbContext)
    //  {

    //  }
}

This is the main Repository class.

public abstract class Repository<TEntity, TKey, TContext>
    : IRepository<TEntity, TKey, TContext>
    where TEntity : class, IEntity<TKey>
    where TContext : DbContext
{
    protected TContext _dbContext;
    protected DbSet<TEntity> _dbSet;

    public Repository(TContext context)
    {
        _dbContext = context;
        _dbSet = _dbContext.Set<TEntity>();
    }
    
    // other methods such as Add, Remove etc.
}

My understanding is that since the context parameter is specified in KeyboardMouseActivityRepository, it only works for that specified context. How can I modify it so it works for both DbContext files and I can do data operation in both DB at the same time?

question from:https://stackoverflow.com/questions/66060093/simultaneous-data-operation-in-sqlite-and-sql-server-databases-using-entity-fram

Answer 1

The repository you have defined is typed per-DbContext. If you want to have a repository that can update two known DbContext implementations then you can back off the Generic approach for the DbContexts and implement the repository to accept one of each in the constructor:

public abstract class Repository<TEntity, TKey>
    : IRepository<TEntity, TKey>
    where TEntity : class, IEntity<TKey>
{
    protected SqlAppDbContext _sqlContext;
    protected SqlLiteAppDbContext _sqlLiteContext;
    protected DbSet<TEntity> _sqlDbSet;
    protected DbSet<TEntity> _sqlLiteDbSet;

    public Repository(SqlAppDbContext sqlContext, SqlLiteAppDbContext sqlLiteContext)
    {
        _sqlContext = sqlContext ?? throw new ArgumentNullException("sqlContext");
        _sqlLiteContext = sqlLiteContext ?? throw new ArgumentNullException("sqlLiteContext");
        _sqlDbSet = _sqlContext.Set<TEntity>();
        _sqlLiteDbSet = _sqlLiteContext.Set<TEntity>();
    }
    
    // other methods such as Add, Remove etc.
}

Note that you will want to investigate and implement something like TransactionScope to help ensure that operations done via the repository are mutually committed or rolled back. For instance if you have code that attempts to update data in both DbSets and SaveChanges, if one succeeds and the other fails for any reason, usually the expectation would be they both roll back. Reads I expect would prioritize one DbSet over the other, but expect if you were to want to support something like a fail-over or situational load from one server or the other you will run into issues if it is at all possible that entities fetched from one DbContext are ever married up with entities fetched from the other. (entities loaded by _sqlContext cannot be associated with entities loaded by _sqlLiteContext) When updating entities and associating them via navigation properties you will be loading everything twice or playing a very dangerously error prone game of detaching and reattaching entities betewen DbContexts.

I would advise against using a Generic Repository pattern /w EF. This will paint you into various corners that will limit many of the capabilities that EF can provide for optimizing queries, working with projections, and performing operations like pagination, filtering, sorting, etc. efficiently without a lot of extra code or introducing pretty complex code into the repository.

Overall I wish you luck with the project, however a requirement and design like this will be a nest of hungry dragons for your time and sanity. :)