CODEWITHSUNDEEP
cachingentity-framework-4.1sqldependency
sagesky36
sagesky36

Reputation: 4692

SqlDependency not working with Entity Framework

I have an issue when trying to start the SqlDependency.

The error informs me: Keyword not supported: 'metadata'.

The connectionstring is the following when retrieved from the immediate window right before it crashes.

?objectContext.Connection.ConnectionString
 "metadata=res://*/YeagerTech.csdl|res://*/YeagerTech.ssdl|res://*/YeagerTech.msl;provider=System.Data.SqlClient;provider connection string=\"data source=Bill-PC;initial catalog=YeagerTech;integrated security=True;multipleactiveresultsets=True;App=EntityFramework\""

Here is the code. It crashes on the Start method. Apparently, it doesn't think the EF connectionstring is valid. Any idea of how I can correctly use this?

YeagerTechEntities dbContext = new YeagerTechEntities();

            ObjectContext objectContext = ((IObjectContextAdapter)dbContext).ObjectContext;

            SqlDependency.Start(objectContext.Connection.ConnectionString);

Upvotes: 1

Views: 3714

Answers (2)

sagesky36
sagesky36

Reputation: 4692

Actually, the code snippet that I got it to work is the following:

YeagerTechEntities dbContext = new YeagerTechEntities();

ObjectContext objectContext = ((IObjectContextAdapter)dbContext).ObjectContext;

Application["dbContext"] = dbContext;

objectContext.Connection.ConnectionString =    
   ConfigurationManager.ConnectionStrings["YeagerTechEntities"].ConnectionString;

SqlDependency.Start(((System.Data.EntityClient.EntityConnection)objectContext.Connection)
   .StoreConnection.ConnectionString);

YeagerTechEntities is the EF connectionstring.

Upvotes: 1

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364369

Because EF connection string is not valid for SqlDependency. It works only with EntityConnection but SqlDependency uses SqlConnection. So you must either use direct connection string in your dbContext or extract database connection string from entity connection.

Either:

var connectionString = dbContext.Database.Connection.ConnectionString;

Or

var connectionString = ((EnityConnection)objectContext.Connection).StoreConnection.ConnectionString;

Anyway EF doesn't play very well with SqlDependency. SqlDependency expects that you write SQL queries yourselves and have them under your control.

Upvotes: 4

Related Questions