Using timestamp from sqlserver in entity framework to only get changes rather then reloading whole table/view

Question

I would like to have optimized version of my WinForms C# based application for slower connections. For this reason I wanted to introduce timestamp column into all tables (that change) and load most of things the first time it's needed and then just read updates/inserts/deletes that could have been done by other people using application.

For this question to have an example I've added a timestamp column into Table called Konsultanci. Considering that this table might be large I would like to load it once and then check for updates/inserts. In a simple way to load it all I do it like this:

    private void KonsultantsListFill(ObjectListView listView)
    {
        using (var context = new EntityBazaCRM(Settings.sqlDataConnectionDetailsCRM)) {

            ObjectSet<Konsultanci> listaKonsultantow = context.Konsultancis;
            GlobalnaListaKonsultantow = listaKonsultantow.ToList(); // assign to global variable to be used all around the WinForms code.
        }
    }

How would I go with checking if anything changed to the table? Also how do I handle updates in WinForms c#? Should I be checking for changes on each tabpage select, opening new gui's, saving, loading of clients, consultants and so on? Should I be refreshing all tables all the time (like firing a background thread that is executed every single action that user does? or should it only be executed prior to eventual need for the data).

What I'm looking here is:

1. General advice on how to approach timestamp problem and refreshing data without having to load everything multiple times (slow connection issues)

2. A code example with Entity Framework considering timestamp column? Eventually code to be used prior executing something that requires data?

Answer 1

Timestamps are not well suited to help you detect when your cache needs to be updated. First off, they are not datetimes (read here) so they don't give you any clue as to when a record was updated. Timestamps are geared more towards assisting in optimistic locking and concurrency control, not cache management. When trying to update your cache you need a mechanism like a LastModified datetime field on your tables (make sure it's indexed!) and then a mechanism to periodically check for rows that have been modified since the last time you checked.

Regarding keeping your data fresh, you could run a separate query (possibly on another thread) that finds all records with the LastModified > than the last time you checked and then "upsert" (update or insert) them into your cache context. Another mechanism with Entity Framework is to use the Context.Refresh() method.