C # WPF Entity Framework 6 Database Sync

Question

Scenario:

I have two MySQL databases:

• Large database
• Small customer base

Examples of tables:

Large database user:

text username

• int id
• Varchar entry
• varchar password
• ... much more fields

Customer database user

• int id
• int UNIQUE api_id (id from the master)
• Varchar entry
• varchar password

Problem: I need to synchronize the databases, but I do not know how to do this in the best way. I read this question , but it is quite old, and does not cover my case. I communicate with the main database through the REST API, direct connection is not an option.

My sync algorithm

• Loading and deserializing data from a REST API (e.g. / api / users /) into a 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 buddy list
  • If an object with ApiUser.id exists, rewrite all fields
  • Else create a new object
• Save changes

My code is:

  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 a problem with deleted objects from the main database, my algorithm does not cover this case.

Answer 1

I assume that all user interactions or automated transactions are performed only in the main database (otherwise you will need some kind of merge replication that is not trivial).

Regarding deleted objects, there are several options. In any case, your main database should distribute information about remote objects to the client databases.

1. Each object stores information if it is deleted.

In this case, you can use the soft delete option, which can be easily implemented in EF by overriding the DbContext OnModelCreating and SaveChanges methods (you can find many examples of code with implementation on the network). This option has some drawbacks - you probably have a rather complicated domain model with relations between entities, so you should take care of soft deletion of child objects when deleting their parent. If you have different front-end applications for the main database and for the client databases, you will have to update them all to apply the new soft delete property (for example, IsDeleted). But synchronization itself for (soft) remote objects in this case is very simple, since on the client side only one additional property needs to be updated.

2. New tables for remote objects.

In this case, you will need to create an additional table for each object and insert the identifier value before deleting any object. You will have to override DbContext SaveChanges to intercept objects in EntityState.Deleted state.

---

Regarding the first question, it depends on what you want to improve. If you expect to have many records in tables, you should consider introducing an additional field to update only those records that were really updated in the main database - you can choose between values ​​of the int (entity), DateTime or guid type, respectively , depending on what is most suitable for your case.

If you want to separate the problem of processing properties by properties, you can create special entity models for synchronization purposes only, deserialize your data for these objects, and then use AutoMapper to update your applications, 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();