C# WPF Entity Framework 6 Synchronization databases

Question

Scenario:

I have two MySQL databases:

1. Big master database
2. Small client database

Example tables:

Big Database User:

text username

• int id
• varchar login
• varchar password
• ... a lot more fields

Client Database User

• int id
• int UNIQUE api_id (id from master)
• varchar login
• varchar password

Problem: I need to synchronize databases, but i don't know how to do that in the best way. I read this question, but it's quite old, and not cover my case. I communicate with master database via REST API, direct connection is not the option.

My synchronization Algorithm

• Download and deserialize data from REST API ( for example /api/users/ ) to List of ApiUser objects

    public class ApiUser {
      int id;
      string login;
      string password; 
    }

    public class User{
      int id;
      int api_id;
      string login;
      string password; 
    }

• Iterate over List of Apiusers
  • If entity with ApiUser.id exist overwrite all fields
  • Else create new entity
• Save changes

My code:

public void syncUsers(List <ApiUser> ApiUsers)

    {
        using (var db = new dbEntities())
        {
            ApiUsers.ForEach(apiUser =>
            {
                var dbUser = db.client
                    .Where(c => c.api_id == apiUser.id)
                    .SingleOrDefault();

                if (dbUser == null)
                {
                    var userObj = new user()
                    {
                        api_id = apiUser.id,
                        login = apiUser.login,
                        password = apiUser.password
                    };
                    db.client.Add(userObj);
                }
                else
                {
                    dbUser.api_id = apiUser.id,
                    dbUser.login = apiUser.login,
                    dbUser.password = apiUser.password
                }

            });

            db.SaveChanges();
        }
    }

Question: How to do it better? I have problem with deleted entities from master database, my algorithm does not cover cover this case.

Answer 1

I assume that all user's interaction or automated transactions are done only in the master database (otherwise you would need some kind of merge replication, which is not trivial).

As for deleted entities, there are several options. In any case, your master database must propagate information about deleted entities to the client databases.

1. Each entity holds the information if it's deleted.

In this case, you can use soft delete option which can be easily implemented within EF by overriding the DbContext's OnModelCreating and SaveChanges methods (you can find many code samples with the implementation on the net). This option has some drawbacks, too - you probably have quite complex domain model with relations between entities so you have to take care of soft deleting child entities when you delete their parent. If you have different front-end applications for master database and for the client databases you'll have to upgrade all of them to take new soft delete property (e.g. IsDeleted) into action. But the synchronization itself for the (soft) deleted entities is in this case very simple as it takes only one additional property to be updated at client's side.

2. New tables for deleted entities.

In this case, you'll have to create an additional table for each entity and insert Id value before you delete any entity. You'll have to override DbContext's SaveChanges to intercept entities in the EntityState.Deleted state.

---

Regarding the first question it depends what you want to enhance. If you expect to have many records in tables you should think about introducing additional field to update only those records which were really updated at the master database - you can chose between int (entity version), DateTime, or guid value, respectively, depending what is the most suitable for your case.

If you want to separate the concern of dealing with property-by-property updating you can create special entity models only for the sync purposes, deserialize your data to those objects and then use AutoMapper for updating your entites, for example:

UPDATE

dbUser = Mapper.Map<User>(apiUser);
db.Set<User>().Attach(dbUser);
db.Entry(dbUser).State = EntityState.Modified;
db.SaveChanges();

ADD

dbUser = Mapper.Map<User>(apiUser);
db.client.Add(dbUser)
db.SaveChanges();