Syncing foreign tables
-
23 kwietnia 2012 11:23
Hi,
As with any good database design I have a full relational model, with foreign key relationships.
I now wish to use the sync framework toolkit to sync data betwen this database and all client data stores (IPad in our circumstance).
This all sounds good and works fine for main tables that can be filtered, i.e. our userorders tables that has 500,000 rows, here each ipad will only need to download a fraction of these 500,000, i.e. 10 or 20. this can be esily achieved by filtering by userid.
The issue I have whoever is how to filter against the tables further down the entity tree, i.e. oderdetails, which quite rightly does not have a userid column to filter against.
Thus I am wodnering how to do this, to my knowledge I have thought of:
- adding a userid column in such tables, ending up with orderdetails.userid, but this violates the db design and in any case is messy requiring losts of tsql to repopulate the userid column
- recursively go though each order, get the orderid and then recursively get all the oderdetails rows, but again requiring lots of more code on client to recursivley call
- somehow modify the query in linq for oderdetails so that given a userid i can do joins on the tables, i.e. user-->userorder-->orderdetails and filter the orderdetails based on the userid column. This in effect means having userid as a filter parameter for orderdeatils and by joing to user via order table can use this userid as a filter
I like point (3), but could not see any suitable entry points in the services to do this.
This must be a common problem and wondered what the framework exposes to do this, i.e. does it somehow do 3), or some kind of recursive syncing based on foireign key relationships?
I think you may be able to do in the client in linq by doing the aforementioned joins in a query submission, but would'ne this surely be better in the service, although I may wrong and cannot even do in the client code?
Any help would be greatly appreciated.
Wszystkie odpowiedzi
-
24 kwietnia 2012 07:21
For anyone reading this I solved this by reading another realted forum post:
http://social.msdn.microsoft.com/Forums/en-US/synclab/thread/89da883c-3f37-4ea6-87d0-4b2a86dc9075
In which mahjayar explains you can modify the stored procedure that is called for selects, i.e. in his words:
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
Obviously be careful when re-porvisioning your db as your sp will be recreated again
I hope this helps others, as to me this must be something that is a common requirement
- Oznaczony jako odpowiedź przez xcfrt 24 kwietnia 2012 07:21
-
25 kwietnia 2012 12:29Moderator
you'll also find another approach using the filterclause without hacking the SPs. if you can model the join using IN clause...
http://social.msdn.microsoft.com/Forums/en/synclab/thread/f8516d4d-2352-468f-82e3-b06f18effaaa
- Oznaczony jako odpowiedź przez xcfrt 25 kwietnia 2012 12:40
-
25 kwietnia 2012 12:40Excellent June Thanks, much appreciated:-)
