NHibernate SysCache2 - dependency breaks cache

Question

Company class:

class Company {
  public virtual int Id { get;set;}
  public virtual string Name { get;set;}
  public virtual int IntProp { get;set;}
}

Companies table:

CREATE TABLE Companies (
  Id INT PRIMARY KEY,
  Name NVARCHAR(50) NOT NULL,
  IntProp INT NULL );

Here is my web.config:

<syscache2>
    <cacheRegion name="RefData"></cacheRegion>
    <cacheRegion name="Company">
        <dependencies>
            <commands>
                <add name="CompanyCommand"
                         command="SELECT * FROM Companies WHERE Deleted = 0"
        />
            </commands>
        </dependencies>
    </cacheRegion>
</syscache2>

Problem: other entities in the RefData cache region (which never change, think countries, etc.) are cached fine. Company, on the other hand, is not.

Mr. TA · Accepted Answer · 2014-05-14T17:49:29.590

The solution consisted of solving multiple problems:

IntProp was nullable on the table, and property was not nullable int. This led to NHibernate not being able to set NULLs when getting a row, so the property value became 0. When session flushed, it saw a modification (NULL to 0) and threw exceptions about modifying readonly data. (This is a NH design flaw, IMO)
The dependency query SELECTs all columns (*), which is not supported by the dependency mechanism. Instead, each column has to be listed. (This fails quietly, which again is a SQL design flaw, IMO)
The dependency query FROM clause has table name but omits the schema name, which is also not supported. Adding dbo. fixed it. I spent 4 hours to find this. (Again, quiet failure, SQL flaw)
After going to production lane, user permission bugs appeared. Those can be fixed by following http://social.technet.microsoft.com/Forums/exchange/en-US/99321f54-1fef-4860-9fe9-5966a46fe582/once-for-all-right-permissions-for-sqldependency-please

Hope this helps someone.

PS. Per request, final result:

class Company {
  public virtual int Id { get;set;}
  public virtual string Name { get;set;}
  public virtual int? IntProp { get;set;}
}

<syscache2>
    <cacheRegion name="RefData"></cacheRegion>
    <cacheRegion name="Company">
        <dependencies>
            <commands>
                <add name="CompanyCommand"
                         command="SELECT Id, Name, IntProp FROM dbo.Companies WHERE Deleted = 0"
        />
            </commands>
        </dependencies>
    </cacheRegion>
</syscache2>

Brilliant. Thanks for this, thanks for sharing your experience. — Radim Köhler, May 15 '14 at 03:23
Note that the 'quiet' failures are quite loud in reality. SQL Server will complain by immediately invalidating the query with the [`Info`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationeventargs.info(v=vs.110).aspx) as [`Invalid`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationinfo(v=vs.110).aspx) and the [`Source`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlnotificationeventargs.source(v=vs.110).aspx) will be `Statement`. Is your app that is ignoring them... — Remus Rusanu, May 22 '14 at 11:21
The Query Notification restrictions are listed at http://technet.microsoft.com/en-us/library/ms181122.aspx — Remus Rusanu, May 22 '14 at 11:23
@RemusRusanu yes and no. Yes SQL provides a certain feedback to indicate the dependency failed, and no, it's not "loud" enough, so it's still "quiet". As evident from my context, NH SysCache2 wraps around it (like any other ORM/DAL would). If SQL threw an exception, it would've bubbled up to the caller. Instead it relies on "return value" approach - which I still think is a flaw. — Mr. TA, May 23 '14 at 15:54

NHibernate SysCache2 - dependency breaks cache

1 Answers1

Linked