How to use Generic Repository, Unit of Work pattern with multiple EDMX for Multiple Databases

Question

I'm on the development of a project which has 3 MS SQL Server databases.

I use,

• EntityFramework 6.4.0,
• Generic Repository,
• Repository Unit of Work Pattern.
• ASP.Net MVC
• Multipe Entity Data Models

I need to save changes into multiple tables in multiple DBs as one transaction (Using one SaveChanges() method)

I have done projects using the above patterns using a single database, single UoW class, single generic repository. Now I'm stuck with multiple DBs.

My generic repository Class as follows

    public class GenericRepository<TEntity, TContext> : IGenericRepository<TEntity, TContext> 
        where TEntity : class 
        where TContext : DbContext
    {
        internal DbContext context;
        internal DbSet<TEntity> dbSet;

        public GenericRepository(DbContext context)
        {
            this.context = context;
            this.dbSet = context.Set<TEntity>();
        }


        public IEnumerable<TEntity> GetAll()
        {
            return context.Set<TEntity>().ToList();
        }


        public virtual IEnumerable<TEntity> Get(
            Expression<Func<TEntity, bool>> filter = null,
            Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
            string includeProperties = "")
        {
            IQueryable<TEntity> query = dbSet;

            if (filter != null)
            {
                query = query.Where(filter);
            }

            foreach (var includeProperty in includeProperties.Split (new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
            {
                query = query.Include(includeProperty);
            }

            if (orderBy != null)
            {
                return orderBy(query).ToList();
            }
            else
            {
                return query.ToList();
            }
        }

        public virtual TEntity GetByID(object id)
        {
            return dbSet.Find(id);
        }

        public virtual void Insert(TEntity entity)
        {
            dbSet.Add(entity);
        }

        public virtual void InsertRange(IList<TEntity> entityList)
        {
            dbSet.AddRange(entityList);
        }

        public virtual void Delete(object id)
        {
            TEntity entityToDelete = dbSet.Find(id);
            Delete(entityToDelete);
        }

        public virtual void DeleteRange(IEnumerable<TEntity> lstEntity)
        {
             dbSet.RemoveRange(lstEntity);
        }

        public virtual void Delete(TEntity entityToDelete)
        {
            if (context.Entry(entityToDelete).State == EntityState.Detached)
            {
                dbSet.Attach(entityToDelete);
            }
            dbSet.Remove(entityToDelete);
        }

        public virtual void Update(TEntity entityToUpdate)
        {
            dbSet.Attach(entityToUpdate);
            context.Entry(entityToUpdate).State = EntityState.Modified;
        }
    }

My Unit Of Work class as follows;

    public class UnitOfWork<TContext> : IDisposable where TContext : DbContext, new()
    {
        private DbContext context;

        private IGenericRepository<TBL_RC_DATA_PHL_EmployeeData_SIN, DbContext> tbl_Data_EmployeeData_sin;
        public IGenericRepository<TBL_RC_DATA_PHL_EmployeeData_SIN, DbContext> Tbl_Data_EmployeeData_sin
        {
            get
            {
                if (this.tbl_Data_EmployeeData_sin == null)
                {
                    this.tbl_Data_EmployeeData_sin = new GenericRepository<TBL_RC_DATA_PHL_EmployeeData_SIN, DbContext>(context);
                }
                return tbl_Data_EmployeeData_sin;
            }
        }

        private IGenericRepository<TBL_RC_DATA_PHL_EmployeeData_PHL, DbContext> tbl_Data_EmployeeData_phl;
        public IGenericRepository<TBL_RC_DATA_PHL_EmployeeData_PHL, DbContext> Tbl_Data_EmployeeData_phl
        {
            get
            {
                if (this.tbl_Data_EmployeeData_phl == null)
                {
                    this.tbl_Data_EmployeeData_phl = new GenericRepository<TBL_RC_DATA_PHL_EmployeeData_PHL, DbContext>(context);
                }
                return tbl_Data_EmployeeData_phl;
            }
        }

        private IGenericRepository<TBL_RC_DATA_PHL_EmployeeData_ENG, DbContext> tbl_Data_EmployeeData_eng;
        public IGenericRepository<TBL_RC_DATA_PHL_EmployeeData_ENG, DbContext> Tbl_Data_EmployeeData_eng
        {
            get
            {
                if (this.tbl_Data_EmployeeData_eng == null)
                {
                    this.tbl_Data_EmployeeData_eng = new GenericRepository<TBL_RC_DATA_PHL_EmployeeData_ENG, DbContext>(context);
                }
                return tbl_Data_EmployeeData_eng;
            }
        }

        public UnitOfWork()
        {
            context = new TContext();
        }

        public void Save()
        {
            context.SaveChanges();
        }

        private bool disposed = false;
        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    context.Dispose();
                }
            }
            this.disposed = true;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
    }

My service layer classes as follows

  public class svcEmployeeData
    {
        private UnitOfWork<DAL.RCPlusEntities> unitOfWork;

        public svcEmployeeData()
        {
            unitOfWork = new UnitOfWork<DAL.RCPlusEntities>();
        }

        public void updateEmployees(TBL_RC_DATA_PHL_EmployeeData_SIN sin, TBL_RC_DATA_PHL_EmployeeData_ENG eng)
        {
        //updating Business Logic goes here

        unitOfWork.Tbl_Data_EmployeeData_sin.Update(sin);        
        unitOfWork.Tbl_Data_EmployeeData_eng.Update(eng);
        unitOfWork.Save();

        }
}

How to transform this architecture into 3 EDMXes by 3 databases...?

Thanks in advance.

Answer 1

Below is based on the assumption that structure of all three databases is same. If structure is different, I will recommend to avoid this much generalization. Instead, maintain the different data access layer for each database.

The reason why you cannot reuse same class for other database is that, you are creating instance of DbContext in UnitOfWork constructor like below:

private DbContext context;
public UnitOfWork()
{
    context = new TContext();
}

Instead, if you accept the instance of DbContext in constructor, I think the same should work with any database.

private DbContext context;
public UnitOfWork(DbContext context)
{
    this.context = context;
}

Of-course, further you need to alter your service layer to inject the correct instance of DbContext.

By the way, re-think before using too much wrappers over wrappers. Please refer to this post for more details.

Now, as you said:

I need to save changes into multiple tables in multiple DBs as one transaction (Using one SaveChanges() method)

and commented:

I need to handle transactions. If any error occurred when updating the 2nd DB table, Changes on 1st DB tables should be rolled back. Easy to do it with one UnitOwWork.Save()

Consider the point raised by @Holger in comment.

Note: Cross-Database Transactions on SQL-Server are only supported on the same server. – Holger

The SaveChanges method will not help you here. Instead, you can use TransactionScope in service layer to commit/rollback entire batch across the databases.

So, in service layer, it looks something like below:

using (TransactionScope scope = new TransactionScope())
{
    //Create unitOfWorkDb1 here
    unitOfWorkDb1.DoSomething();
    unitOfWorkDb1.Save();

    //Create unitOfWorkDb2 here
    unitOfWorkDb2.DoSomething();
    unitOfWorkDb2.Save();

    scope.Complete();
}

Please refer to this and this post about transaction scope. Also, this article is very informative.