Using the Skip and Take methods to provide paging with the LINQ provider doesn't work. I am using Oracle and this may be some of the issue. Anyway, the description of the error and therefore reason is pretty straight forward.
This is the code... var query = from e in Session.Query<OracleData>() where e.name.ToLower() == exName.ToLower() select e; return query.Skip((pageNum - 1) * pageSize).Take(pageSize).ToList();
And this is the error message... Test method RunTest threw exception: NHibernate.ADOException: could not execute query [ select * from ( select row_.*, rownum rownum_ from ( select oracleinve0_.YAAY as YAAY2_, oracleinve0_.XX as XX2_2_, oracleinve0_.XX2 as XX3_2_, oracleinve0_.RCVSASADT as RCVSASADT2_, oracleinve0_.SASALIFE as SASALIFE2_, oracleinve0_.SASABNM as SASABNM2_, oracleinve0_.KKLKPRFX as KKLKPRFX2_, oracleinve0_.KKLKNMBR as KKLKNMBR2_, oracleinve0_.KKLKSUFX1 as KKLKSUFX9_2_, oracleinve0_.KKLKSUFX2 as KKLKSUFX10_2_, oracleinve0_.ZXCV as ZXCV2_, oracleinve0_.SASAVBCD as SASAVBCD2_, oracleinve0_.SASAPOIK as SASABUIL13_2_, oracleinve0_.ASDZ as ASDZ2_ from GHJK oracleinve0_ where (upper(oracleinve0_.XX) is null) and (0 is null) or upper(oracleinve0_.XX)=0 ) row_ where rownum <=2) where rownum_ >3 ] Name1 - Value:abcd ---> Oracle.DataAccess.Client.OracleException: ORA-01008: not all variables bound
If you look towards the end of the generated query it is clear to see that the paging related variables are not assigned at all. (p2, p3)
Environment
None
Activity
Show:
Matt Davis
August 3, 2010 at 4:17 AM
Because we are using NHibernate 3.0.0.1001 I have raised this as a new issue (NH-2265) under the root NHibernate project since it now includes linq.
Matt Davis
August 2, 2010 at 8:45 AM
We are using NHibernate 3.0.0.1001
Matt Davis
August 2, 2010 at 8:42 AM
I can confirm that the same thing happens when using Single()/SingleOrDefault() or First()/FirstOrDefault(). This does not happen when using SetMaxResults with an IQuery or ICriteria.
You get a "not all variables bound" error if you are using the Oracle date access components or an "illegal variable name" error if using the oracle Ado.net components.
Andrew A Ritz
May 6, 2010 at 11:46 AM
I forgot to mention that an equivalent approach using QueryOver works correctly.
Using the Skip and Take methods to provide paging with the LINQ provider doesn't work. I am using Oracle and this may be some of the issue. Anyway, the description of the error and therefore reason is pretty straight forward.
This is the code...
var query = from e in Session.Query<OracleData>()
where e.name.ToLower() == exName.ToLower()
select e;
return query.Skip((pageNum - 1) * pageSize).Take(pageSize).ToList();
And this is the error message...
Test method RunTest threw exception:
NHibernate.ADOException: could not execute query
[ select * from ( select row_.*, rownum rownum_ from ( select oracleinve0_.YAAY as YAAY2_, oracleinve0_.XX as XX2_2_, oracleinve0_.XX2 as XX3_2_, oracleinve0_.RCVSASADT as RCVSASADT2_, oracleinve0_.SASALIFE as SASALIFE2_, oracleinve0_.SASABNM as SASABNM2_, oracleinve0_.KKLKPRFX as KKLKPRFX2_, oracleinve0_.KKLKNMBR as KKLKNMBR2_, oracleinve0_.KKLKSUFX1 as KKLKSUFX9_2_, oracleinve0_.KKLKSUFX2 as KKLKSUFX10_2_, oracleinve0_.ZXCV as ZXCV2_, oracleinve0_.SASAVBCD as SASAVBCD2_, oracleinve0_.SASAPOIK as SASABUIL13_2_, oracleinve0_.ASDZ as ASDZ2_ from GHJK oracleinve0_ where (upper(oracleinve0_.XX) is null) and (0 is null) or upper(oracleinve0_.XX)=0 ) row_ where rownum <=2) where rownum_ >3 ]
Name1 - Value:abcd
---> Oracle.DataAccess.Client.OracleException: ORA-01008: not all variables bound
If you look towards the end of the generated query it is clear to see that the paging related variables are not assigned at all. (p2, p3)