Entity Framework - Underlying data (in database) change notification

Question

I'm using the Entity Framework to manipulate data in a database with success so far.

However, I would like to have more than one application playing with the data at the same time (concurrent edition).

Is there a way to get notified when the data in the database changes?

I saw a solution using DML trigger, but I would want to know if there is other ways to achieve that and if yes, what is the best solution to do use.

Regards,

Nic

EDIT

Maybe my questions was not clear enough, I'll try to illustrate it by an example.

• Application#1 is using entity framework on database#1
• Application#2 is also using entity framework on database#1
• Application#1 changes the entity model which is reflected by a change in the underlying table of the database#1
• I want Application #2 to get notified of this change so it can have consistent/up2date data.

Answer 1

Perhaps you should think about usage of EF in your application. EF's context should be used for as shortest period of time as possible:

• Create context
• Load data
• Modify data
• Save data
• Drop context

Because of internal implementation (IdentityMap, UnitOfWork) long living context is not a good choice and with short living context you don't want mentioned behavior at all. Even in desktop application you should use approach like context per form. You load data, you present data to your user and till that time only user can modify data and push save button - it is application responsibility to deal with concurrency issues somehow (timestamp). Automatic modification of data which are part of running unit of work is pretty bad idea - what if user already modified data? Will you overwrite his changes?

Edit:

You can read more about impelementation of ObjectContext here.

I can imagine scenarios where data update notification to client applications are needed. It can be read-only real time data displaying - for example stock trading information. But in such case you need something more powerful. It is not scenario for client calling ORM to get data but the scenario for client subscribing to some service / middle tier which handles data retrieval and fast change notification.

For simple scenarios where you only need to refresh data in semi-real time fashion you can use polling - your client will call the query again within few seconds and use StoreWins strategy. Any notification strategy is outside scope of EF - you have to implement it as trigger, sql dependency, publish subscribe pattern or something else. Even with notification you will only be able to handle some event and requery the data.

Again if you want to reduce data transfer with polling you need some service / middle tier which will allow some level of caching (you can also try WCF Data Services).

Answer 2

At the database level, you can put a timestamp column on your table and use that to tell if a row has been updated since retrieval. This could be checked by a trigger as you suggest, or by a monitoring system in your C# code, or if you just want to prevent overwriting changes, you can write update sprocs that check the timestamps, and use those to save your entities.

No matter which option you choose, you'll have to decide ahead of time how you want to manage conflicts.

Timestamp-based concurrency control