SqlDependecy with Entity Framework Core

Question

User-1798249116 posted

Hi Devs, 

I´m planning to implement a notification system using ASP.NET Core + EF Core + MVC, I make same research and I noticed that the most examples uses SqlDependecy but didn´t find nothing about implementing SqlDependecy with Entity Framework.

I wanna know how to use SqlDependecy with Entity Framework, is it possible?

Best Regards

Answer 1

User-1716951449 posted

i dont have experience in ef core

but i have some experience in sqldependency, which rely on the sql server broker service. it's a bad design/approach

the broker service has this kind of notion "poison" sql thingy, like sql error cause by data operation such as inserting duplicate keys etc, which upon a certain number of occurences, it will auto disable the broker service, which make totally no sense to me.

if u really diehard wanna to attempt it, i suggest u use separate and different sqlconx with no data operation through it, and it should never encounter error with it

have fun

Answer 2

User-1838255255 posted

Hi vanilson,

According to your description, as far as I know, now the asp.net core doesn't support the SQL dependency track. But you could use ServiceBrokerListener. For more details, please check the following tutorials:

Application Insights ASP.NET Core SQL Dependency Track:

https://blog.fujiy.net/post/application-insights-aspnet-core-sql-dependency-track 

ServiceBrokerListener:

https://github.com/dyatchenko/ServiceBrokerListener 

Best Regards,

Eric Du

Answer 3

User-1798249116 posted

Hi Eric Du,

Thank you for your answer. I´m trying to use the ServiceBrokerListener but the OnDataChange event is not getting fired. Here is the code:

    private const string CONNECTION_STRING = "Server=PC-NAME;Database=Database;Trusted_Connection=True;MultipleActiveResultSets=true; Integrated Security=false;User ID=used_id;Password=password";
    private const string DATABASE_NAME = "db_name";
    private const string TABLE_NAME = "table_name";
    private const string SCHEMA_NAME = "dbo";

    private void RegisterNotification()
    {
        sqlDependency.TableChanged += OnDataChange;
        sqlDependency.Start();
    }
    private void OnDataChange(object sender, SqlDependencyEx.TableChangedEventArgs e)
    {
        //Code
    }
    public IActionResult Create(){
         RegisterNotification();
    }

I make some search and the solutions that I found didn´t solve my problem. 

Best Regards

Answer 4

User-1798249116 posted

Problem solved, here´s the code:

private const string CONNECTION_STRING = "Server=LFTCMCPTP83;Database=Database;Trusted_Connection=True;MultipleActiveResultSets=true; Integrated Security=false;User ID=used_id;Password=password";
private const string DATABASE_NAME = "db_name";
private const string TABLE_NAME = "table_name";
private const string SCHEMA_NAME = "dbo";
private SqlDependencyEx sqlDependency = new SqlDependencyEx(CONNECTION_STRING, DATABASE_NAME,
TABLE_NAME, SCHEMA_NAME);
listener.TableChanged += (o, args) =>
{
   //Code...
};
listener.Start();
listener.Stop();

Best Regards