none
Problem with Synchronizing Relative tables in Sync Framework 2.1 RRS feed

  • Question

  • Hello!!

    I am trying to synchronize Sql Express 2008 with Sql Compact 3.5 with help of Sync Framework 2.1

    I have 2 tables

    • Customers(PK Id , string Name )
    • Orders (PK Id , int Price , string Name , FK Customer_Id)

    I Use the following code

     

    public static void CreateScope(string userName)
    {
    	var serverConn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=Test; Integrated Security=True");
    
    	var scopeDescription = new DbSyncScopeDescription(string.Format("Customer-{0}", userID));
     
    	var customersTable = SqlSyncDescriptionBuilder.GetDescriptionForTable("Customers", serverConn);
    	var ordersTable = SqlSyncDescriptionBuilder.GetDescriptionForTable("Orders", serverConn);
    	
    	ordersTable.Constraints.Add("FK_Customer_Id", "Customers", "Orders", "Id", "Customer_Id");
     
    	scopeDesc.Tables.Add(azmanotTable);
    	scopeDesc.Tables.Add(customersTable);
     
    	var serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
    	serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
    
    	serverProvision.Tables["Customers"].AddFilterColumn("CustumerName");
    	serverProvision.Tables["Customers"].FilterClause = string.Format("[side].[CustomerName] = '{0}'", userName);
    serverProvision.Apply(); }

    During the Sync I do receive filtered "Customers" table but i also receive full orders table when i wanted only subset of this data , I mean only orders that belong to specific customer.

    I have tried all the options i found in web for example to change insert order adding foreign key manually but still no success.

    Thanks in Advance.

     



    • Edited by Robob Saturday, May 28, 2011 7:09 AM
    Saturday, May 28, 2011 12:45 AM

Answers

  • I found Solution using Nested Query 

    serverProvision.Tables["Customers"].AddFilterColumn("CustumerName");
    serverProvision.Tables["Customers"].FilterClause = string.Format([side].[CustumerName] = '{0}'", userName);
    
    serverProvision.Tables["Orders"].AddFilterColumn("Customer_Id");
    serverProvision.Tables["Orders"].FilterClause = string.Format( "[side].[Customer_Id] IN (select Id from Customers where CustumerName ='{0}')", userName); 
    

    Is it the only possible solution because it means that each time table Orders needs to be synchronized additional sql query is executed

     

    Thanks.


    Sabi Reuven
    Saturday, May 28, 2011 9:22 AM

All replies

  • Sync Framework syncs tables independently regardless of the foreign key relationships.

    in your case, you have to add a filter on the order's table as well.

     

    e.g.,

    serverProvision.Tables["Orders"].AddFilterColumn("CustomerID");
    serverProvision.Tables["Orders"].FilterClause = string.Format("[side].[CustomerID] = '{0}'", userID);

     

    Saturday, May 28, 2011 1:22 AM
    Moderator
  • O.K  how it can be done if i don't know the custumerID (I made a mistake in the example and instead of filtering based on name i filtered based on id , the example is already updated ).

    Thanks


    Sabi Reuven
    Saturday, May 28, 2011 7:12 AM
  • I found Solution using Nested Query 

    serverProvision.Tables["Customers"].AddFilterColumn("CustumerName");
    serverProvision.Tables["Customers"].FilterClause = string.Format([side].[CustumerName] = '{0}'", userName);
    
    serverProvision.Tables["Orders"].AddFilterColumn("Customer_Id");
    serverProvision.Tables["Orders"].FilterClause = string.Format( "[side].[Customer_Id] IN (select Id from Customers where CustumerName ='{0}')", userName); 
    

    Is it the only possible solution because it means that each time table Orders needs to be synchronized additional sql query is executed

     

    Thanks.


    Sabi Reuven
    Saturday, May 28, 2011 9:22 AM
  • as i have mentioned earlier, Sync Fx doesnt consider the table relationships when picking up changed rows. so filtering the child rows based on rows in the parent table is a workaround.


    Saturday, May 28, 2011 12:03 PM
    Moderator
  • as you and JuneT mentioned, your approach would work here and will work as long as the FI relationships among the synced table are not complex to have mutiple level nested queries -- which would be big impact to perf if you can manage to have the right functionalities of it.

    there are other folks posted in the forun to modify the table design so that each of them will have the filtered column(s) to avoid sunc nexted queries, in case you will need an alternative solutions for this case.

     

    thanks

    Yunwen


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Sunday, May 29, 2011 5:52 AM
    Moderator