NHibernate 2nd lvl cache, custom query, sqldialect

Question

I got trunk version of NH and FNH. When i try to add 2nd level cache, some parts of NHibernate forgets about chosen sqldialect.

---

Initial configuration:

var cfg = Fluently.Configure()
  .Database(MsSqlConfiguration.MsSql2008
    .ConnectionString(connectionString)
    .DefaultSchema("dbo")
    .UseReflectionOptimizer()    
  .Mappings(m => ................);

Guilty custom query:

var sql = @"with Foo(col1,col2,col3)
              as (select bla bla bla...)
            Select bla bla bla from Foo";

list = Session.CreateSQLQuery(sql)
  .AddEntity("fizz", typeof(Fizz))
  .SomethingUnimportant();

When i change configuration to:

var cfg = Fluently.Configure()
  .Database(MsSqlConfiguration.MsSql2008
    .ConnectionString(connectionString)
    .DefaultSchema("dbo")
     .UseReflectionOptimizer()
     .Cache(c=>c
       .UseQueryCache()
         .ProviderClass<HashtableCacheProvider>())
       .ShowSql())
     .Mappings(m => ................);

Query throws error (WITH clause was added in mssql2008):

The query should start with 'SELECT' or 'SELECT DISTINCT'

[NotSupportedException: The query should start with 'SELECT' or 'SELECT DISTINCT'] NHibernate.Dialect.MsSql2000Dialect.GetAfterSelectInsertPoint(SqlString sql) +179 NHibernate.Dialect.MsSql2000Dialect.GetLimitString(SqlString querySqlString, Int32 offset, Int32 limit) +119 NHibernate.Dialect.MsSql2005Dialect.GetLimitString(SqlString querySqlString, Int32 offset, Int32 last) +127 NHibernate.Loader.Loader.PrepareQueryCommand(QueryParameters queryParameters, Boolean scroll, ISessionImplementor session) +725 NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) +352 NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) +114 NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) +205

---

Any ideas what exactly confuses nhibernate and how to fix it?

---

Guilty NHibernate code (in NHibernate/Dialect/MsSql200Dialect.cs):

private static int GetAfterSelectInsertPoint(SqlString sql)
{
  if (sql.StartsWithCaseInsensitive("select distinct"))
  {
    return 15;
  }
  else if (sql.StartsWithCaseInsensitive("select"))
  {
    return 6;
  }
  throw new NotSupportedException
    ("The query should start with 'SELECT' or 'SELECT DISTINCT'");
  }
}

Looks that .SetMaxResults(123) causes this. Fortunately, i can unbound that query.

Hopefully that will fix this.

Answer 1

Just as i predicted - unbounding select is acceptable workaround.

Deleted SetMaxResults and it works.

Answer 2

Just had the same problem using a similar query which has a WITH clause.

Unfortunately, my query populates a grid, with paging, so I have to keep SetMaxResults.

My solution was to rewrite using a Derived Table:

var sql = @"with Foo(col1,col2,col3)
              as (select x1, x2, x3 from x join y blabla)
            Select col1, col2, col3 from Foo
            join B on B.col1 = Foo.col1";

becomes

var sql = @"Select col1, col2, col3 from 
           (select x1 as col1, x2 as col2, x3 as col3 
            from x join y blabla) as Foo
           join B on B.col1 = Foo.col1";

Just to allow NHibernate to insert the " TOP x " string after the "select" string (6 characters from the begining)... No comment :(

T