Depending on your database combinations of SetMaxresults and SetFirstsresult gives freaky results with Distinctrootentity for instance given this mapping:
Code:
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Sample" assembly="Sample">
<class name="Company">
<id name="Id">
<generator class="identity" />
</id>
<property name="Name" />
<set name="Employees" cascade="save-update">
<key column="CompanyID"/>
<one-to-many class="Employee"/>
</set>
</class>
<class name="Employee">
<id name="Id">
<generator class="identity" />
</id>
<property name="Name" />
<many-to-one name="Company" class="Company" not-null="true" column="CompanyID" />
</class>
</hibernate-mapping>
And filling some data in:
Code:
Company c1 = new Company();
c1.Name = "Company1";
Company c2 = new Company();
c2.Name = "Company2";
Employee e1 = new Employee();
e1.Name = "Employee1";
Employee e2 = new Employee();
e2.Name = "Employee2";
Employee e3 = new Employee();
e3.Name = "Employee3";
c1.Employees.Add(e1);
e1.Company = c1;
c2.Employees.Add(e2);
c2.Employees.Add(e3);
e2.Company = c2;
e3.Company = c2;
sess.Save(c1);
sess.Save(c2);
And executing this code:
Code:
IList<Company> companies =
sess.CreateCriteria(typeof (Company)).SetFetchMode("Employees", FetchMode.Join).Add(Expression.Eq("Name", "Company2")).SetResultTransformer(CriteriaUtil.DistinctRootEntity).SetMaxResults(1).List<Company>();
Debug.Assert(companies.Count == 1, "The number of companies was not 1");
Debug.Assert(companies[0].Employees.Count == 2, "The number of employees were not 2 but " + companies[0].Employees.Count);
Vil result in the second assertion fail on MySQL, on MSSQL 2005 however et seems to work, but if you throw a SetFirstResult in there it will give you wrong number of elements back in some cases too.
The reason is quite simple in the mySQL case (the MSSQL is a little complicated because nHibernate uses ROWNUMBER function to do some magic) in mySQL the generated join query is appended with a "LIMIT 1" which cuts of the resultset at this point before we have retrieved all our duplicate Company rows with different Employee information.
For your viewing pleasure is here the complete SQL generated by nHibernate:
Code:
SELECT this_.Id as Id0_1_, this_.Name as Name0_1_, employees2_.CompanyID as CompanyID__3_, employees2_.Id as Id3_, employees2_.Id as Id1_0_, employees2_.Name as Name1_0_, employees2_.CompanyID as CompanyID1_0_ FROM Company this_ left outer join Employee employees2_ on this_.Id=employees2_.CompanyID WHERE this_.Name = ?p0 limit ?p1; ?p0 = 'Company2', ?p1 = '1'