How to implement "get replaced latests elements" using ADO.NET data services?

Question

Suppose I have lists of entities (for example, a list of TODO elements) that can be changed by several users, deleted and added at the same time.

So, to set up synchronization between all clients, I want each client (based on AJAX) to request changes every xx seconds. Since the list may grow, I do not want to make a full request each time, but only request changed items (items can be updated, deleted or new).

Is this possible with ADO.NET data services? If so, how to implement this if I use Entity Framework on the server?

I examined the use of the ASP.NET cache to store change items when the change operation is sent to the data service and has its own web method that will return the latest changes from the last request of the last client (requests can be tracked through the client session object ASP.NET). However, I do not know how to indicate the change state of an individual element (for example, deleted or updated or inserted) in the result set.

An excellent solution will also allow the client to request changes in many entities in the same server call.

Any input will be significantly accelerated.

Best regards, Egil.

Answer 1

What comes to my mind is to use the generation number for the table. Give the TODO table a column called generation, and (mentally) relate to it a global and constant variable number of generations. (It is probably best to put this in a table in the database).

Each time an update or insert is performed, the generation number is incremented and placed in the generation number column for the updated or inserted record.

When records are first read from the database and placed on a web page, the current generation number is also retrieved and cached locally (i.e., placed in a JavaScript variable). Subsequent reads with AJAX use the where clause, which filters only records with a generation number greater than the locally cached generation number. This ensures that only new updates and deletes are received. Then the locally cached generation number is updated.

Removing is a little trickier. One way to do this is to delete buried records for a limited period, say, after 21 minutes. You have a bit of a column labeled “Tombstone” and a column of “datetime”. When a record is deleted, the column has the generation number set in the same way as for insertion or deletion, the tombstone bit is set, and the datetime is set to the current time using GetDate (). An AJAX client request can then select entries with the tombstone flag set and remove them from the list on the client side. If the client is encoded in such a way that after every 20 minutes a full page is refreshed or an ajax call that receives each record, then the database task can be executed every minute and clear (i.e. delete) tombstones older than 20 minutes.

EDIT: you do not need to save the generation number separately, you can use the sql MAX function above the generation column.