Getting Incorrect syntax near 'OFFSET' error in NHibernate.Linq query

Question

I am getting following error in my ASP.NET MVC 4.0 Application with NHibernate v4.0.0 (.Net Framework 4.0). This error shows in NHibernate.Linq query

Incorrect syntax near 'OFFSET'.
Invalid usage of the option FIRST in the FETCH statement.

IN THIS LINE

Line 23:         public IList<Post> Posts(int pageNo, int pageSize)
Line 24:         {
Line 25:             var posts = _session.Query<Post>()  //here
Line 26:                                   .Where(p => p.Published) 
Line 27:                                   .Skip(pageNo * pageSize)

I have found some similar posts on SO and on other sites. but, they are suggesting that use SQL SERVER 2012 instead of 2008. Yes my sql server version is 2008. but, I have created another application using ASP.NET MVC 5 (.Net Framework 4.5) and NHibernate v3.3.1 and it works great in same database and same sql server version.

Some similar post:

1. “Incorrect syntax near 'OFFSET'” modift sql comm 2012 to 2008
2. Pagination query for mssql server 2008 Throwing Incorrect syntax near 'OFFSET'
3. Making sense of 'OFFSET/FETCH' in SSMS 2012

So, I don't think that the problem is in my sql server version at least in my case.

I am not executing sql query directly into ssms or through command object. I am using NHibernate.Linq query.

Full NHibernate Query:

 var posts = _session.Query<Post>()
                              .Where(p => p.Published)
                              .Skip(pageNo * pageSize)
                              .Take(pageSize)
                              .Fetch(p => p.Category)
                              .ToList();

How do I solve this problem. Please guide me.

Please ask me for more information if it is insufficient.

Thanks !!

Answer 1

It seems, that NHibernate is just wrongly instructed to use dialect related to SQL Serer 2012

<property name="dialect">NHibernate.Dialect.MsSql2012Dialect</property>

Just set it to 2008

<property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>

and it will NOT use features from a later version Implement paging (skip / take) functionality with this query