LINQ to SQL - detecting obsolete data

Question

I retrieve the schedule data from the database through LINQ to SQL and therefore use the DataContext object. The data determines when certain actions should be performed and can be updated independently of my service.

I periodically poll the database to see if the schedule is updated and adjust my planning accordingly.

That is, if I knew how to determine if the data was obsolete.

• Do I need to create a new DataContext for each check?
• Do I need to implement my own equality method for type Row and iterate over tables?

Ideally, I would just make a different choice using the same data context, and get a table reference, and then call Equals. I understand that I live on a land of dreams.

Answer 1

Good question!

I found this blog entry on this very issue, where someone wanted to use SqlDependency with LINQ to SQL ...

http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/05/04/9302.aspx

which will go hand in hand with this for WinForms. (You did not specify WinForms or ASP.NET)

Edit - Added

Please note that this only applies to client applications (WinForms, etc.) where there are a very small number of clients or an ASP.NET application. The MSDN documentation states this in their documentation at http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx :

SqlDependency was developed for use in ASP.NET or mid-tier services where there are a relatively small number of servers that have dependencies active against the database. it was not intended to be used in an application client, where hundreds or thousands of client computers have SqlDependency objects configured for a single database server.

However, if you are developing an application with only a few users, this should be acceptable.

Answer 2

Auto-generated classes derived from a DataContext are not intended for long-lived objects. In the vast majority of cases of use, you must create an instance, read the data that you need, and / or make the necessary changes, send, and then forget about the object. It does not directly represent a database connection - creating DataContext instances does not necessarily open multiple database connections.

The consequence of this is that you have to keep in mind that your data is always out of date, especially if a separate process can access the same database. Once you receive the data, another process can immediately update this data. The only way to guarantee volatility is to maintain an open transaction, and I am sure you know the consequences of continuing the transaction for too long is that another process will detect that the database is very unresponsive, as if it were very slow - so use a transaction only for the processing phase, which includes almost only access to the database, which should be atomic.

Thus, in your particular case, the only way to find out if there are any new actions that need to be done is to continue to query the database. You do not need to continue to receive data and compare them using Equals; you can just get all the data that you already have, that is, tell the database about the return of only new rows. Since I do not know your database schema, I am doing everything here, but I am sure this will give you an idea:

 var newActions = db.ScheduledActions.Where(act => !oldActions.Contains(act)).ToList(); if (newActions.Any()) { // ... do something intelligent with the new actions ... oldActions = oldActions.Concat(newActions); // or perhaps oldActions.AddRange(newActions); if oldActions is a List<> } 

Edit: Now I understand that you need to check not only new actions, but also changes to existing actions. Personally, how I implement this is to have a date / time field in the ScheduledActions table that indicates the time of the last change. Then you don't need a custom comparison mapper, but instead, you can use this to find out what has changed. Unfortunately, this means that you need to get all the actions, even those that have been changed. In the following example, I assume that ScheduledActions are identified by a primary key of type string ; change this to int if you use number keys:

 Dictionary<string, ScheduledAction> oldActions = new ...; [...] var allActions = db.Actions.ToList(); var changedActions = allActions.Where(act => !oldActions.ContainsKey(act.Id) || oldActions[act.Id].LastChanged < act.LastChanged).ToList(); if (changedActions.Any()) { // do something intelligent here } 

Answer 3

I have a different answer that is different from my first, but I still think that the first is a valid option in limited situations, so I am sending it separately.

What you are really talking about is handling concurrency problems with LINQ. Microsoft provides guidance here:

http://msdn.microsoft.com/en-us/library/bb399373.aspx

Answer 4

First you need to clearly determine which data (table, columns (columns)) are under your supervision of the change / update, and what exactly happens on the database side when this data changes. What does “schedule been updated” mean in your case - one column in one row changes, but it does not affect the prioritization or a column that affects the changes in the prioritization? You have a clear separation between data that affects planning and data that only affect processing.

The answers to these questions critically determine what can be done even in principle. In the general case, there is no abstract solution to the problem of scrambling.

Once you determine what and what affects the individual data for real planning (determining when something is processed) from the processing date (determining how something is processed), you reduce your problem to two shift points and two reaction points.

Then you can control insert and update on the DB side with sprocs and triggers so that all new things are marked as such. Then you create a sproc that requests only market lines and marks them as "read". When your middle level gets them, it calls another sproc to recognize, and that sproc marks them as old. Variations of this depend on the actual architecture, but this is key — marking and updating the state of the data in place — in SQL . SQL will also need a trigger to return records from "read" to "new" if there is no timely confirmation (this means that your middle level has died).

There is nothing like this in LINQ DC at all. Once you allow this, you can actually fulfill the equivalent of a single request and get only those items that are new with respect to pre-reading.

In addition, what you get from LINQ is not a link to a table in any form — it is a representation of the records received — they will look like table records only if your query does not contain the field that you really need. When you start using sporcs, then it will represent whatever set of results sproc is, which can be joined from 5 tables, but selects only 3 fields.