Error with SqlTableDependency when making requests every second

1

I am migrating to SqlTableDependency to get notifications of changes to the database on my asp.net mvc web system. Previously, I used SqlDependency to get the notifications, but I found it more useful to do that migration.

The problem is a little random because the system works except when a customer deletes data and triggers the dependency and a second later another client selects that same table.

I think it's because the requests are made fast enough but this did not happen to me when I used sqldependency. Here the Error:

System.Data.SqlClient.SqlException: 'The referenced entity 'INSERTED' was modified during DDL execution. Please retry the operation.'

Here my code:

public class SqlMesas
{
    private CreatechContext db = new CreatechContext();

    public void Dependancy()
    {
        var cString = ConfigurationManager.ConnectionStrings["CreatechContext"].ConnectionString;
        SqlTableDependency<Mesa> dependency = new SqlTableDependency<Mesa>(cString, "Mesas");
        dependency.OnChanged += DataOnChange;
        dependency.Start();
    }

    private async void DataOnChange(object sender, RecordChangedEventArgs<Mesa> e)
    {
        try
        {
            if (e.ChangeType != ChangeType.None)
            {
                MesasController controller = new MesasController();
                var data = await controller.GetMesasOrden();
                var hub = GlobalHost.ConnectionManager.GetHubContext<OrdenMeseroHub>();
                await hub.Clients.All.updateMesa(data);
                await hub.Clients.All.changeMesa("OK");
                Dependancy();
            }
        }
        catch (Exception)
        {
            throw;
        }
    }

Is it possible to solve this? or continued using sqldependency since this one did it.

note: I still do not do anything with the data that returns sqltabledependency because I'm still trying it ..

    
asked by denifer santiago fernandez 31.10.2018 в 20:50
source

0 answers