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:

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 !!

If you have grandchildren collections than use `fetchmany` instead `fetch` — Just code, Jun 26 '15 at 04:43
`Fetch` should be used for the references and `fetchmany` for the collections — Just code, Jun 26 '15 at 04:44
yes but, as I have mentioned in my question the same code and same work is working fine for different NHibernate version — Shell, Jun 26 '15 at 10:19

score 8 · Accepted Answer · edited May 23 '17 at 11:53

8

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

edited May 23 '17 at 11:53

Community

1
1

answered Jun 26 '15 at 06:02

Radim Köhler

122,561
47
239
335

Thanks @Radim you just solve my query. You are right i have configured sql server version for NHibernate incorrectly. – Shell Jun 29 '15 at 17:35
Glad to see NHibernate is working for you. Good luck it is amazing tool ;) – Radim Köhler Jun 29 '15 at 17:36
same problem with EF. Check for the `ProviderManifestToken` tag in your edmx files. – Sebas Apr 02 '18 at 11:24
oh, and yeah, "Update Model" will also update "ProviderManifestToken" automatically! So keep an eye on this file most importantly if the version of sql server you are using in your development environment is different than the live one. – Sebas Apr 02 '18 at 11:44

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

1 Answers1