none
Synchronizing data between SQL servers using EF RRS feed

  • Question

  • Hello,

    I have already posted this in SQL Server related forum, but since the issue is relevant to EF I decided to repost here. I aven't got any good answer for few days in the other forum.

    I have designed a database whose MDF file will be copied to remote offices, so basically I will have different databases wth the same scheme. However, some tables from these databases will have to contain the same data. First I was happy because I knew it was easy to sync them using RowVersion columns in each table, but then I remembered that primary key columns in these tables (columns named "ID") are also identity columns. So I have no idea on how to synchronize them in way that they are identical. With same IDs and everything. Also I am doing this through Entity Framework, which sits between the SQL Server 2008 R2 Express and .NET Framework 4 WCF Service. Any clues?

    Note that this is a one-way sync, remote offices need to repliate these tables from the main database but they are not able to modify them and write changes back.
    Friday, December 16, 2011 10:09 AM

All replies

  • Hi,

    It seems the SQL Server side might be still better. Try perhaps a SQL admin group if you previously tried a SQL programming group.

    See http://msdn.microsoft.com/en-us/library/ms151198.aspx. SQL Server has replication built in capabilities or the sync framework mentioned on the same page could likely help...

    Technically speaking you can force the id (SET IDENTITY_INSERT) so keeping the same identity value is not an issue (because you don't add on subscribers so identity values on always created on the same machine and it is possible to insert them in the read only tables).

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Friday, December 16, 2011 12:25 PM
  • Hello,

    thank you for your answer.

    I was aware of that possibility but my issue is that I want to do all this via EF. Another thing that just popped onto my mind is that I could slightly modify the db schema so remote locations' ID columns don't have identity specifications. That way I could just perform a simple one-way sync. However, I am not sure if that would cause some inconsistency withing the EDMX file and problems when saving changes to the db?

    Friday, December 16, 2011 1:46 PM
  • Hi dejancg,

    I think you can try to retrieve the same records from different Context(for different connection): Synchnize Context1 to Context2(Context1----->Context2)

    1. Detach the records from Context1 and Context2

    2. Attach Context1's records to Context2

    3.Call objextContext.ApplyCurrentValues() methods: http://msdn.microsoft.com/en-us/library/dd487246.aspx

    4.Call SaveChanges.

    BTW, I haven't test code, thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Alan_chenModerator Monday, December 26, 2011 2:46 AM
    • Unmarked as answer by dejancg Monday, January 9, 2012 8:58 AM
    Tuesday, December 20, 2011 8:55 AM
    Moderator
  • Hello Alan,

    thank you for your answer, it is very interesting. I will look into it and then get back to you with the feedback.

    Tuesday, December 20, 2011 2:13 PM
  • Hello,

    unfortunately it is still early to mark as answer. We don't want to mark an answer just for the sake of having the question seem answered. I can't confirm that this would work because if I add a new edmx file which would link to a database with the same table names, it will cause, as expected, ambiguity errors.

    Is there any other way of having 2 db contexts in a project without having 2 edmx files?

    Monday, January 9, 2012 9:05 AM
  • Hi,

    You should be able to use a different namespace (see the EDMX properties Window) so it should work. Another option could be to use code first rather than EDMX files.

    As you can use the fluent API to describe the model you should be able to use the same model and have a flag to allow to describe the column either as an identity value or not.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Monday, January 9, 2012 10:10 AM
  • Hi,

    thanks for the hint. It worked after I have created a new folder in my project and added an edmx file to it. Vs automatically assigned the edmx to the namespace named by the folder I created.

    About Code First Fluent API.. well the first thing is that my db design is huge, it would take a significant portion of time in order to port the edmx to Code First. And the second thing is that I used a trick to call a custom function from EF, I am not sure if that would be possible using CF Fluent API.

    And finally, here is my example code, which fails:

     

    namespace Common
    {
        public class Sync
        {
            public static void ExecuteSync()
            {
                MyService.MainDB.Entities DBMain = new MyService.MainDB.Entities();
                MyEntities DB = new MyEntities();
    
                // Detach from DBMain
                DetachEntity<MyService.MainDB.Table1>(DBMain.Table1);
    
                // Detach from DB
                DetachEntity<Table1>(DB.Table1);
    
                // Attach DBMain objects to DB
                AttachEntityToDB<MyService.MainDB.Table1>(DB, "Table1", DBMain.Table1);
    
                // Save
                ApplyChangesToDB<Table1>(DB, "Table1", DB.Table1);
            }
    
            private static void DetachEntity<T>(ObjectSet<T> ObjSet) where T: class
            {
                foreach (var obj in ObjSet)
                {
                    ObjSet.Detach(obj);
                }
            }
    
            private static void AttachEntityToDB<T>(MyEntities DB, string entitySetName, ObjectSet<T> ObjSet) where T: class
            {
                foreach (var obj in ObjSet)
                {
                    DB.AttachTo(entitySetName, obj);
                }
            }
    
            private static void ApplyChangesToDB<T>(MyEntities DB, string EntityName, ObjectSet<T> ObjSet) where T: class
            {
                foreach (var obj in ObjSet)
                {
                    DB.ApplyCurrentValues<T>(EntityName, obj);
                }
            }
        }
    }
    

    The code fails when I try to attach MainDB.Table1 records to DB.Table1. The error message is as follows:

     

     

    The member with identity 'Entities' does not exist in the metadata collection.
    Parameter name: identity

     


    *EDIT*

    According to how I understand entities, I can't attach an entity which doesn't have the EntityKey corresponding to the entity collection. In this case EntityKey.EntityContainerName values are different, the main database has got the "Entities" container name, while my database has got "MyEntities" container name. Then if I try to change the EntityContainerName manually I get bumped by "EntityKey values cannot be changed once they are set.".

    • Edited by dejancg Monday, January 9, 2012 3:07 PM
    Monday, January 9, 2012 2:32 PM