Hql + SetFirstResult + SetMaxResult + Lazy Loading

Question

I have this query

  var hql = @"from Table1 tbl1
                            left join fetch tbl1.Table2";

 var c =session.CreateQuery(hql).SetFirstResult(1).SetMaxResults(5)
                            .List<Table1>().ToList();

It keeps on dieing.

NHibernate.Exceptions.GenericADOException was unhandled by user code
  Message=Could not execute query[SQL: SQL not available]
  Source=NHibernate
  SqlString=SQL not available
  StackTrace:
       at NHibernate.Impl.SessionImpl.List(String query, QueryParameters queryParameters, IList results)
       at NHibernate.Impl.SessionImpl.List[T](String query, QueryParameters parameters)
       at NHibernate.Impl.QueryImpl.List[T]()
       at MvcApplication1.Controllers.Default1Controller.ThenInHql() in MvcApplication1\MvcApplication1\Controllers\Default1Controller.cs:line 152
       at MvcApplication1.Controllers.Default1Controller.Index() in MvcApplication1\MvcApplication1\Controllers\Default1Controller.cs:line 21
       at lambda_method(Closure , ControllerBase , Object[] )
       at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
       at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
       at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12()
       at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
  InnerException: System.ArgumentNullException
       Message=Value cannot be null.
Parameter name: source
       Source=System.Core
       ParamName=source
       StackTrace:
            at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
            at NHibernate.Engine.QueryParameters.CreateCopyUsing(RowSelection selection)
            at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters)
            at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results)
            at NHibernate.Impl.SessionImpl.List(String query, QueryParameters queryParameters, IList results)
       InnerException:

Edit

nhibernate profiler is telling me that it is like a casting problem?

ERROR: 
The value "System.Object[]" is not of type "MvcApplication1.Models.Table1" and cannot be used in this generic collection.
Parameter name: value

Baz1nga · Accepted Answer · 2011-11-22T05:16:08.790

1

NHibernate ICriteria’s SetFirstResult is ZERO BASED! First Result might indicate that it starts with 1 but it doesnt. SetFirstResult(1).SetMaxResults(5) is probably not what you’re trying to do.

change it to SetFirstResult(0).SetMaxResults(5)

edited Nov 22 '11 at 05:16

answered Nov 21 '11 at 19:46

Baz1nga

15,485
3
35
61

What is o/p? What I don't understand if I get rid of SetFirstResult() and SetMaxResults() and do the same query. It will run and stick everything in table1 and table1 will have collection of Table2s.So why can it figure it out in this scenario? So if I do option 2 I have to specify every column I need from table1 and 2? – chobo2 Nov 21 '11 at 20:10
srry I think misunderstood your question.. updated the answer see if that helps – Baz1nga Nov 22 '11 at 05:16
something to ponder upon: http://stackoverflow.com/questions/2461658/nhibernate-2nd-lvl-cache-custom-query-sqldialect – Baz1nga Nov 22 '11 at 05:17

Hql + SetFirstResult + SetMaxResult + Lazy Loading

1 Answers1