EF4 and SQLDependency Object

Question

I am trying to use EF4 with the SQLDependency object with not much luck.

I have created my own queue and service and want my WPF application to monitor this queue for data changes so that I can update the UI (opted for trying this rather than constantly querying the database).

Even though on application startup I call;

SqlDependency.Start(connectString, "NewResultAddedQueue");

My EF4 repository implementation throws an exception stating;

{"When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance."}

Firstly, am I trying to achieve something which is not possible with EF4 or is there another approach I can take to allow my application to listen for data changes from SQL 2005??

Answer 1

First, you cannot use your own queue and service if you have more than one instance of the WPF application running. The SqlDependency infrastructure works per appdomain and if you share a queue then the instances (appdomains) will steal notifications from one another resulting in runtime mayhem. Unless you know exactly what you're doing, just use the default implementation (the just-in-time deployed service/queue/procedure).

The error message is pretty clear: 'When using SqlDependency without providing an options value yada yada yada'. So you do not provide an options value, therefore the default is used:

The notification request options to be used by this dependency. null to use the default service.

At the same time you're overwriting the default service in the .Start() call. As I said, better get the default case working first untill you're sure you understand what's going on, before venturing into non-default customized behavior, specially with something so difficult to grok as The Mysterious Notification.

Have a look at LinqToCache for how to use Query Notifications with LINQ. The article SqlDependency based caching of LINQ Queries explains how this integration works with LinqToSQL and LinqToEF, and also why you cannot actually use it with EF if LINQ is involved.

• David: thanks for your answer. I tried using LinqToCache and still get the same exception thrown??
• Remus Rusanu: Try initializing the SqlDependency w/o a custom service/queue first and see if you can get a simple SqlCommand to work.
• David: tried that and got it working, as you stated Query Notifications don't work with LinqToEF / Entity Framework. Thanks.