none
Entity framework comparing two same tables in two different databases

    Question

  • Hello

    Currently i am working on an application its main purpose is to sync two data bases with the same table's.

    In my application i have added two entity data models to connect to both databases.

    I am looking for a method through which i can compare the objects from these two databases and find out if there is any new record or any record has been updated\modified (in Database_1).

    Currently i am comparing using a if condition inside a nested for each loop, and looking for a easier and better way to do this.



    Please mark those posts as answer which answers your question. Faraz

    • Moved by Shanks Zen Thursday, December 13, 2012 6:57 AM Appropriate forum for this issue (From:Visual Basic General)
    Wednesday, December 12, 2012 3:43 PM

Answers

  • Hi,

    Database synchronisation / replication isn't easy to get right and if you get everything to work you are likely to be in a maintenance nightmare. IMHO key is keeping it simple. If you can try to stay away from multiple database that all change and instead have one master that changes through an application and keep the rest of the databases as synchronised read-only databases. Best is not to write the synchronisation yourself.

    Three options you have before writing the synchronisation yourself:

    If you really, really, really  want to build it yourself expanding on your for eaches and if statements there are few basic things to consider.

    • When do I trigger the update ? if you trigger after every change from the application it can cause to much work and waiting time for the application, I would recommend a windows service dedicated for synchronizing.
    • Do you really need bi-directional updates ? If you don't you can keep the synchronisation algorithm a lot simpler.
    • When I have bi-directional updates and I have a conflict who wins ? The last changed entry might be a choice.
    • When you need bi-directional updates and said that the last entry wins how do you detect that a line was deleted last and should be removed from the other database instead of adding it again to database it was just deleted from. An idea is not to delete the line form the database through the application but set a deleted flag (you application has to ignore this record from than on) real deltion only happens with a seperate cleaning function or through the synchronisation tool.

    Than if you made all these choices (bi-directional, last wins, delete flag) you could use balanced line as an algorithm to update both tables. Please note both tables need to sorted by id and you have to keep the last edited date.

    Basically the balance line algorithm steps through both tables:

    • if id of the table1 record is lower than the id from the table2 record it adds the table1 record to table2 and gets the next record from table1,
    • if id of the table1 record is higher than the id from the table2 record it adds the table2 record to table1 and gets the next record from table2
    • if the id's of both records match the oldest is updated with the newest and the next records are gotten from both tables.  (can also be a delete)


    Hope this helps,

    Here to learn and share. Please tell if an answer was helpful or not at all. This adds value to the answers and enables me to learn more.

    About me

    • Marked as answer by Faraz Zone Thursday, December 13, 2012 10:09 AM
    Thursday, December 13, 2012 8:26 AM

All replies

  • Faraz, 

    The term around this is replication

    Be aware it is not easy.

    http://en.wikipedia.org/wiki/Replication_(computer_science)

    SQL Server has build in features for this.

    http://msdn.microsoft.com/en-US/library/ms151198(v=sql.110).aspx


    Success
    Cor

    Wednesday, December 12, 2012 4:12 PM
  • I am trying to implement something similar to this + some other stuff before syncing the data.

    I have almost 90 tables and each of them have more then 20-48 fields, comparing each of them in if condition is too awkward so i was thinking to find some solution for it.

    Thank you for your reply core.


    Please mark those posts as answer which answers your question. Faraz


    • Edited by Faraz Zone Wednesday, December 12, 2012 5:29 PM
    Wednesday, December 12, 2012 5:28 PM
  • Hi Faraz,

    We have a forum ADO.NET Entity Framework and LINQ to Entities discuss EF issue, in order to provide better support, I'll move this thread.

    Thanks for understanding.


    Shanks Zen
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, December 13, 2012 6:57 AM
  • thank you for moving this thread to the right forum.

    Please mark those posts as answer which answers your question. Faraz

    Thursday, December 13, 2012 7:32 AM
  • Hi,

    Database synchronisation / replication isn't easy to get right and if you get everything to work you are likely to be in a maintenance nightmare. IMHO key is keeping it simple. If you can try to stay away from multiple database that all change and instead have one master that changes through an application and keep the rest of the databases as synchronised read-only databases. Best is not to write the synchronisation yourself.

    Three options you have before writing the synchronisation yourself:

    If you really, really, really  want to build it yourself expanding on your for eaches and if statements there are few basic things to consider.

    • When do I trigger the update ? if you trigger after every change from the application it can cause to much work and waiting time for the application, I would recommend a windows service dedicated for synchronizing.
    • Do you really need bi-directional updates ? If you don't you can keep the synchronisation algorithm a lot simpler.
    • When I have bi-directional updates and I have a conflict who wins ? The last changed entry might be a choice.
    • When you need bi-directional updates and said that the last entry wins how do you detect that a line was deleted last and should be removed from the other database instead of adding it again to database it was just deleted from. An idea is not to delete the line form the database through the application but set a deleted flag (you application has to ignore this record from than on) real deltion only happens with a seperate cleaning function or through the synchronisation tool.

    Than if you made all these choices (bi-directional, last wins, delete flag) you could use balanced line as an algorithm to update both tables. Please note both tables need to sorted by id and you have to keep the last edited date.

    Basically the balance line algorithm steps through both tables:

    • if id of the table1 record is lower than the id from the table2 record it adds the table1 record to table2 and gets the next record from table1,
    • if id of the table1 record is higher than the id from the table2 record it adds the table2 record to table1 and gets the next record from table2
    • if the id's of both records match the oldest is updated with the newest and the next records are gotten from both tables.  (can also be a delete)


    Hope this helps,

    Here to learn and share. Please tell if an answer was helpful or not at all. This adds value to the answers and enables me to learn more.

    About me

    • Marked as answer by Faraz Zone Thursday, December 13, 2012 10:09 AM
    Thursday, December 13, 2012 8:26 AM
  • hello keesdijk

    thank you for your responce, and you are correct this really is a night mare, i have done 5 tables and the line of codes have already exceed 1200.

    I am creating it in a wcf service which will then be hosted in a windows service.

    I am thinking of creating two different services for syncing data from db1 to db 2 and from db2 to db1.

    The service might sync data after some predefined time interval(2-3 hrs) or it might stay in a constant loop. (yet to be decided)


    Please mark those posts as answer which answers your question. Faraz

    Thursday, December 13, 2012 9:44 AM
  • Hi,

    You are welcome.

    Please don't even think about writing two services, the possibilities of circular or simultaneous updates  and just the number of moving parts in your application should be enough reason not to try this.

    The number of tables should not be direct proportional to your code, write a generic table updater and feed the different tables to it.


    Hope this helps,

    Here to learn and share. Please tell if an answer was helpful or not at all. This adds value to the answers and enables me to learn more.

    About me

    Thursday, December 13, 2012 10:04 AM
  • Thank you

    I will keep that in mind when i get to that step and will reply back in this thread.


    Please mark those posts as answer which answers your question. Faraz


    • Edited by Faraz Zone Thursday, December 13, 2012 10:10 AM
    Thursday, December 13, 2012 10:08 AM