0

We have a import / bulk copy batch that runs in the middle of the night, something like

using (var tx = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromMinutes(1)))
{
   //Delete existing
   ...
   //Bulk copy to temp table
   ...
   //Insert
   ...
   tx.Complete();
}

We also have a cache that uses SqlDependency. But it triggers allready on the Delete statement making the Cache empty for a few seconds between the delete nad the reinsert. Can I configure SqlDependency to only listen to commited data?

SqlDep. code

private IEnumerable<TEntity> RegisterAndFetch(IBusinessContext context)
{
    var dependency = new SqlDependency();
    dependency.OnChange += OnDependencyChanged;

    try
    {
        CallContext.SetData("MS.SqlDependencyCookie", dependency.Id);
        var refreshed = OnCacheRefresh(context, data.AsEnumerable());
        var result = refreshed.ToArray();
        System.Diagnostics.Debug.WriteLine("{0} - CacheChanged<{1}>: {2}", DateTime.Now, typeof(TEntity).Name, result.Length);

        return result;
    }
    finally
    {
        CallContext.SetData("MS.SqlDependencyCookie", null);
    }
}

OnDependencyChanged basily calls above RegisterAndFetch method

Query:

protected override IEnumerable<BankHoliday> OnCacheRefresh(IBusinessContext context, IEnumerable<BankHoliday> currentData)
{
    var firstOfMonth = DateTime.Now.FirstDayOfMonth(); // <-- Must be a constant in the query
    return context.QueryOn<BankHoliday>().Where(bh => bh.Holiday >= firstOfMonth);
}
Anders
  • 17,306
  • 10
  • 76
  • 144
  • hmm you would expect your transaction scope to prevent this? is the delete correctly using the transaction scope? – Ewan Mar 13 '15 at 10:22
  • ahh what query do you use for your dependancy notification? apparently there are issues – Ewan Mar 13 '15 at 10:26
  • Well I guess the event should trigger, but the read should wait until the transaction is complete? See update for Query – Anders Mar 13 '15 at 11:43
  • no I mean the SqlDependency sql command. apparently there are restrictions on the type of select such as not using read uncommitted https://msdn.microsoft.com/en-us/library/wf4282bx(v=vs.110).aspx – Ewan Mar 13 '15 at 12:09
  • http://stackoverflow.com/questions/19791152/sqldependency-subscription-not-working-when-using-isolationlevel-readuncommitted – Ewan Mar 13 '15 at 12:10
  • Well the CMD is generated by EF, will check you links, thanks – Anders Mar 13 '15 at 12:15
  • why oh why do people still use EF?! – Ewan Mar 13 '15 at 12:16
  • its the only certified ORM to use at this work place – Anders Mar 13 '15 at 12:31
  • I prefer raw SqlClient *old skool* – Ewan Mar 13 '15 at 12:32
  • I have verified that its the Write side that causes the problem, I replaced the raw SQL with Entity framework and the SQLdep. is no longer doing dirty reads. So now I have to fidn out why the transaction is not working (EF is too slow for bulk copy) – Anders Mar 16 '15 at 08:29

0 Answers0