Valid T-sql filterclause, wrong sync results
-
2011年8月3日 下午 01:24
We are using sync framework 4.0 ctp with isolatedStorage for Silverlight. We have users that have Contacts. Each user syncs his own contacts to the server. So the filterclause for syncing is the userId on table Contact. A contact entity may "represent" another user (i.e I have a Contact for john smith, but John smith is actually registered in the service so the contact represents him). So a Contact has a "RepresentsUserId" column. Two users may connect (setting ConnectionStatus=5 instead of 1) based on some business logic.
If User A has a contact C that represents User B, and this contact has a "connectionStatus=5", then when User A syncs I want him to also get User B's contacts. That's the idea. It boils down to this filterclause.
<SyncTable Name="[Contact]" GlobalName="Contact" SchemaName="" IncludeAllColumns="true" FilterClause="[side].UserId IN ((select RepresentsUserId from [Contact] where UserId = @UserId and ConnectionStatus = 5) UNION (select @UserId)) ">
This theoretically selects not only the @userid argument but also the representsUserIds that are connected with @userId, in effect widening the query results.We have done extensive testing. This fiter only works ...sometimes. Say that User A is connected with B and vise versa. What usually happens is that ONE of the two gets the other's contacts and then the other gets only ONE contact (totally weird, user B may only get User A's contact that represents user B and nothing else).
If the problematic client (the one with missing contacts) deletes his local storage and syncs again, the correct number of contacts is synced.
We have debugged everything both in the server and the client. On one hand the server chooses not to sync back the correct number of contacts based on the filterclause, but only sends back one contact. On the other hand this "decision" changes once the local isolatedStorage is purged in the problematic client.
Please advise, our whole project is at risk. Thank you!
所有回覆
-
2011年8月3日 下午 01:56版主does it always work on the first sync and you only encounter a problem on subsequent sync?
-
2011年8月3日 下午 02:02
On first sync user A and user B get their contacts. Then I go and change User A's contact for b and User B's contact for A, and set those to connectionStatus=5. I do this by hand in the database to eliminate any other logical errors. This means that are "connected" and on next sync should get each other's contacts.
Only one of the two users gets the other's contacts. The other user (say user B) will only get one contact from user A, the one that represents User B.
This behavior changes from test to test as to who will get the expected num of contacts. It is either chaotic or there some complex chain of causality that I don't grasp (who synced first, who connected with how first, whatever).
-
2011年8月3日 下午 02:11版主have you tried synching user A and B from different machines?
-
2011年8月3日 下午 02:15
I only have that option. This test is done using 2 different development machines that run a server and the client. Servers are looking at a common sql server. So user A and user B sync on 2 differt pcs.
-
2011年8月3日 下午 02:33版主
if you dont mind, fire me off an email at : junet at live dot com dot au
and include a schema of the table with some sample data (maybe 5 rows) then a simulation of the update that you are making.
