Syncing Related Tables
-
Monday, June 28, 2010 3:19 PM
How would you specify the following database model using the sync config file?
- User (parameterized using a userid)
- Customers (each customer is owned by a user so I can again use a parameterized userid)
- Orders (each order is tied to a customer so I want to get all orders for the customers assigned to my user.)
Users & Customers are fine I can follow the example, but I can't see how to specify a relationship between Customers and Orders when provisioning the database, so that I only get the orders linked to the customers that are allocated to a user?
thanks
// cmck
All Replies
-
Monday, June 28, 2010 3:38 PMModerator
Just add a FK column to your Orders table that points to the Customers table. Nothing special needs to be done in the config file. Just make sure that the order you specify them in the config file is the right order. i.e User followed by Customers followed by Orders. The CTP service handles the ordering based on what you specify. For the client you would have to use LinqToObject to do a join on the OrdersCollection and CustomersCollection objects on the context and get the relationship your self. Does that help?
Maheshwar Jayaraman - http://blogs.msdn.com/mahjayar- Proposed As Answer by mjayaramModerator Thursday, July 01, 2010 4:49 PM
- Unproposed As Answer by cmck Friday, July 02, 2010 8:09 AM
-
Thursday, July 01, 2010 3:05 PM
There are foreign key relationships between all the entities and they are specified in the recommended order.
The problem is not wiring it up client side the problem is that *all* the orders are sent across in the sync, regardless of whether the customer is available to the user.
How do I filter so that only the orders for the customers assigned to the user are sent during the sync request.
// charlie
-
Thursday, July 01, 2010 4:45 PMModeratorDid you take a look at adding a parameterized filter to the Orders table. Please take a look at the ListSample that we shipped which shows how we specify the parameters in the config file, service and client implementations. It shows how to filter lists based on username.
Maheshwar Jayaraman - http://blogs.msdn.com/mahjayar- Proposed As Answer by mjayaramModerator Thursday, July 01, 2010 4:49 PM
- Unproposed As Answer by cmck Friday, July 02, 2010 8:09 AM
-
Thursday, July 01, 2010 7:55 PM
I've looked at the sample but it doesn't meet my needs the orders table is related only to the customers table (it has no userid only a customer id) and the customers table is related to the user only. I already filter the customers on the userid and this works fine but I need to only have the orders for the users customers synced down.
The orders is a large busy table not some static reference table that changes infrequently so its really not practical to sync down all items and then filter it locally.
Basically I need a dynamic filter so that the orders relating to a users customers are only downloaded? I can post the schema if that would help explain?
// charlie
-
Saturday, July 03, 2010 1:33 AMModerator Now I understand. There are two ways of doing this for the current labs. One is you can add the Userid to the Order table as well and use that to filter. The other way is pass the UserId to the Order table as a parameter query. Then you can look at the "Orders_selectchanges" stored proc on the database after it is provisioned and then modify the join column to do a join with the customer table to include only the customers that match the user id. I will try to find out if this is something that we can plan for the next release.
Maheshwar Jayaraman - http://blogs.msdn.com/mahjayar- Proposed As Answer by M.Bi Monday, July 05, 2010 11:38 AM
- Marked As Answer by Nina HOwner Tuesday, July 06, 2010 6:42 PM
-
Monday, July 05, 2010 6:19 AM
you can add the Userid to the Order table as well and use that to filter. The other way is ... the "Orders_selectchanges" stored proc on the database ... modify the join column ... I will try to find out if this is something that we can plan for the next release.
Comming from an existing application the first choice is not really an option. And the latter one seems quite hackisch but i'm going to try it right away.
I just wanted to second that such relational schema seems pretty common and therefore this topic should definatly be adressed by syncfx framework.
-
Monday, July 05, 2010 12:53 PM
The proposed solution seems pretty hacky and could be easily lost when reprovisioning the db. I would imagine it is a pretty common senario to dynamically partition the data based on table relationships so would really like to see it make the release.
When you say "next release" do you mean next CTP release or next product release ie not in SyncFx 3?
// charlie
-
Tuesday, July 06, 2010 6:41 PMOwner
Hi Charlie,
It will definitly be in SyncFx3.0 timeframe.
Thanks,
Nina
This posting is provided "AS IS" with no warranties, and confers no rights. -
Wednesday, July 07, 2010 10:04 AMThanks Nina.
-
Wednesday, July 28, 2010 10:52 AM
While the suggestion to manipulate the procedure seems like a good workarround at first glance it still has a major caveat: these handcrafted joins are not tracked by the syncfx' change tracking mechanism and therefore changes in the relations (newly or no longer related entities) remain hidden for the clients.
-
Friday, July 30, 2010 5:33 PMModerator
Point taken M.Bi. We realize this is an important extensibility point and will do due planning on this.
Maheshwar Jayaraman - http://blogs.msdn.com/mahjayar
