SqlDependency not working with Entity Framework

Question

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);

Ladislav Mrnka · Accepted Answer · 2011-12-04 15:44:38Z

4

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.

answered Dec 4, 2011 at 15:44

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wonea · Accepted Answer · 2013-05-07 13:18:03Z

1

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.

edited May 7, 2013 at 13:18

wonea

4,99917 gold badges91 silver badges144 bronze badges

answered Dec 11, 2011 at 6:23

sagesky36

4,73221 gold badges88 silver badges134 bronze badges

Collectives™ on Stack Overflow

SqlDependency not working with Entity Framework

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related