SQL Server : find out if a db table has changed (using entity framework)

Question

I would like to find out the best practice for the following scenario: we have a database table created using Entity Framework (code-first). There's a DBContext and a local collection used as cache, corresponding to the data in the table.

We need to find out if and when someone updates the database manually (any CRUD ops) in order to keep the cache in sync with the database at all times. Ops will be accessing the DB and there's no way around it - so it has to be a technical solution and not a BI one. How can this be done?

Thanks.

Answer 1

You could use an SqlDependency Object.

Subscribe to its onChange event and you'll be able to respond when your data changes.

• Enable service broker on your database
• Grant subscribe query notifications to your asp.net account