Geek Answers Handbook

NHibernate ManyToMany and eager loading: weird results for SetFetchmode combined with SetResultTransformer and SetMaxResult

I have a many-to-many relationship that I am trying to extract from impatience:

*.CreateCriteria(typeof(Class1)) .SetFetchMode("Class2", FetchMode.Eager) .SetResultTransformer(new DistinctRootEntityResultTransformer()) .SetFirstResult(20) .SetMaxResult(10) .List<Class1>();

I would like to return lines 20-30, but instead I have 12-18. What for? Since SetResultTransformer is executed AFTER SetMaxResult. It returns 10 lines, starting at line 20, but then it is different (is that a word?), Which leads to lines 12-18. I fully understand what is happening, but I can’t come up with a solution that uses criteria ...

Does anyone have a solution?

+4
source share
4 answers

This is because the SetMaxResults method works at a low level. I mean, SetMaxResults calls the SQL statement that NHibernate generates to contain the TOP or LIMIT .

Of course, this leads to those weird results when you use impatient fetching, since impatient fetchmode leads to JOIN.

So, if you have two tables that are joined together in a one-to-many relationship, the record set returned by NHibernate looks like this, for example

 1 recordA-fromTable1 1 recordX-fromTable2 2 recordA-fromTable1 2 recordY-fromTable2 3 recordB-fromTable1 3 record2-fromTable2

If "TOP 2" is executed in the above record set, NHibernate will be able to extract only one entity from it, since the first 2 records in the result set are actually intended for the same object.

I resolved this using a separate subquery containing the TOP clause, and gets the identifier of the objects to be received.

+1
source

I didn't really try to do this, but it might work.

  *.CreateCriteria(typeof(Class1)) .SetFirstResult(20) .SetMaxResult(10) .CreateCriteria("Class2", JoinType.LeftOuterJoin) .SetResultTransformer(new DistinctRootEntityResultTransformer()) .List<Class1>();

EDIT: This does not work. Use multiquery, multicriteria (or Future when using the new NH build)

0
source

I have the same problem. Adapting the following using separate criteria and subqueries looks as if it could be promising. I am going to do a more thorough test when I receive some time.

http://blogs.taiga.nl/martijn/2008/11/20/nhibernate-criteria-queries-across-multiple-many-many-associations/

0
source 
  public List<Employee> getData(int to, int from, String searchField, String searchString, String searchOper, String sord, String sidx) { Criteria hCriteria = null; List<Employee> viewDataList = null; List<Employee> exactDataList = null; try { hSession = HibernateSessionFactory.getSession(); hTransaction = hSession.beginTransaction(); hCriteria = hSession.createCriteria(Employee.class); if (searchString != null && searchField != null && searchOper != null) { if (searchOper.equalsIgnoreCase("eq")) hCriteria.add(Restrictions .eq(searchField, searchString)); if (searchOper.equalsIgnoreCase("ne")) hCriteria.add(Restrictions.ne(searchField, searchString)); if (searchOper.equalsIgnoreCase("lt")) hCriteria.add(Restrictions.lt(searchField, searchString)); if (searchOper.equalsIgnoreCase("le")) hCriteria.add(Restrictions.le(searchField, searchString)); if (searchOper.equalsIgnoreCase("gt")) hCriteria.add(Restrictions.gt(searchField, searchString)); if (searchOper.equalsIgnoreCase("ge")) hCriteria.add(Restrictions.ge(searchField, searchString)); if (searchOper.equalsIgnoreCase("cn")) hCriteria.add(Restrictions.ilike(searchField, searchString, MatchMode.ANYWHERE)); } if (sord != null && sidx != null && !sidx.equals("")) { if (sord.equals("asc")) hCriteria.addOrder(Order.asc(sidx)); else hCriteria.addOrder(Order.desc(sidx)); } /* hCriteria.setFirstResult(to); hCriteria.setFirstResult(from); */ hCriteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); viewDataList = hCriteria.list(); // for limit exactDataList=viewDataList.subList(from,to); hTransaction.commit(); } catch (Exception e) { hTransaction.rollback(); } finally { try { hSession.flush(); HibernateSessionFactory.closeSession(); } catch (Exception hExp) { } } return exactDataList; }
0
source

More articles:


All Articles