로그인
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
Syncing foreign tables

Respondido Syncing foreign tables

  • 2012년 4월 23일 월요일 오전 11:23
     
     
    로그인하여 투표
    0

    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:

    1. 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
    2. 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
    3. 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.

     

모든 응답

  • 2012년 4월 24일 화요일 오전 7:21
     
     답변됨
    로그인하여 투표
    0

    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

    • 답변으로 표시됨 xcfrt 2012년 4월 24일 화요일 오전 7:21
    •  
     
  • 2012년 4월 25일 수요일 오후 12:29
    중재자
     
     답변됨
    로그인하여 투표
    0

    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

    • 답변으로 표시됨 xcfrt 2012년 4월 25일 수요일 오후 12:40
    •  
     
  • 2012년 4월 25일 수요일 오후 12:40
     
     
    로그인하여 투표
    0
    Excellent June Thanks, much appreciated:-)