views:

228

answers:

1

Hi

Lets say I have a lists of entities (for example, a list of TODO items) that more than one user can change, delete from and add to at the same time.

So to maintane a synchronized listed between all the clients, I want each client (AJAX based) to ask for changes every xx seconds. Since the list can get veeery long, I do not want to do a full request each time, but only ask for the changed items (items can be updated, deleted or new).

Is it possible with ADO.NET Data Services? If so, how do I implement it, if I use Entity Framework on the server?

I have considered using ASP.NETs cache to store change items in when a change operation is submitted to the data service, and have a custom web method that would return the latest changes since the specific clients last request (requests could be tracked through the clients ASP.NETs session object). However, I have no idea how to indicate the change state of the individual item (e.g. deleted or updated or inserted) in the resultset.

A great solution would also make it possible for the client to ask for changes in many entities in the same roundtrip to the server.

Any input will be much appreaciated.

Best regards, Egil.

+1  A: 

The way that springs to my mind is to use a generation number for the table. Give the TODO table a column called generation and (mentally) associate with it a global and persistent generation number variable. (Probably best to put this in a table in the DB).

Every time an update or insert is made the generation number is incremented and put into the generation number column for the updated or inserted record.

When the records are first read from the DB and put on the web page the current generation number is also retrieved and cached locally (i.e. put into a JavaScript variable). Subsequent AJAX driven reads use a where clause which filter only records with a generation number greater than the locally cached generation number. This ensures only new updates and deletes are retrieved. The locally cached generation number is then updated.

Deletes are a bit more tricky. One way to do it is to tombstone deleted records for a limited period, say 21 mins. You have bit column called tombstone and a datetime column. When a record is deleted the column has it's generation number set in the same way as for an insert or delete, the tombstone bit is set, and the datetime is set to the current time using GetDate(). The client's AJAX request can then select records with the tombstone flag set and remove them from the client side list. If the client is coded so that after every 20 mins a full page refresh, or an ajax call that gets each and every record, is enforced then a DB job can run every minute and clean up (i.e. delete) the tombstoned records older than 20 minutes.

EDIT: You actually don't need to separately persist the generation number, you can the sql function MAX over the generation column.

Christopher Edwards
Thanks for the input Christopher. Your idea will definitely work, but I would love to keep the change management out of the database, in my world it is client related and not really relevant to the data.
Egil Hansen
Maybe you could use basically the same scheme without using the DB by caching the resultset in global.asax and adding the generation field etc. to the resultset there. You could then cache the client's generation number in the session or in JS to get the updates/inserts/deletes.
Christopher Edwards
I do need to store my data in a database, but if I could somehow modify the resultset on the fly, it would be great. I am not sure how EF allows me to do that though.
Egil Hansen
Hmm... Could you implement the extra "fields" as additional properties in your data model using partial classes - http://msdn.microsoft.com/en-us/library/bb738612.aspx. They wouldn't be persisted in the DB but would stick around as long as the objects are in the global cache.
Christopher Edwards
Uhm yeah, good idea. Then I would probably need to wrap my entity framework data context that would update the data before the it reaches the DS. Anyway, thanks for the input!
Egil Hansen