Hello,

I have a case like this:

#region description - may not read :)

Two classes - let's call them Person and Address. The relation between them is one-to-many. I am searching persons by a criteria ( criteria.List() ) with added Alias, Criterions, etc...

The criteria is from type Person, the class Person has a collecion of type Address and I create an Alias ( criteria.CreateAlias() ) to Address. Than I add an SimpleExpression to the criteria, which corresponds to the Address.

What's my trouble? Example: There is one Person with two addresses and both of them satisfy the expression (like address.City == London). Then the criteria returns two times the same Person. Of cource, I need it only once - it is the same Person.

#endregion

---------------------

Let me give a simple example:

First class:

public class Client
{
...

private string name;
public string Name
{
get { return name; }
set { name = value; }
}
}

Second class:

public class Organization
{
...

private IList<Client> _clients;
public IList<Client> Clients
{
get { return (IList<Client>)_clients; }
}
}

And this code:

Client client1 = new Client("Client");
Client client2 = new Client("Client"); // the same name

Organization organization1 = new Organization("Organization1");
organization1.Clients.Add(client1);
organization1.Clients.Add(client2);

Save the organization.

Then search for organizations:

ICriteria criteria = session.CreateCriteria(typeof(Organization));

criteria.CreateAlias("Clients", "clients");
criteria.Add(Expression.Eq("clients.Name", "Client"));

IList loadedOrganizations = criteria.List();

And the result is 2 organizations, in fact, two times the same organization. But I need it only once.

---------------------

Please, help :).

My solution at this time is very stupid - removing equal objects from the result :). There should be much better way...

NHibernate will return duplicate instances if outer join is used, just like the original result set from the data source.

One simple solution is telling NHibernate to remove duplicates like this...
[ICriteria].SetResultTransformer(CriteriaUtil.DistinctRootEntity)

/Roger

Thanks, Roger. Exactly what I needed.

Remember that SetMaxResult is a no go when using DistinctRootEntity ResultTransformer :-)

But why, I just tried it and it works fine.

Depending on your database combinations of SetMaxresults and SetFirstsresult gives freaky results with Distinctrootentity for instance given this mapping:



And filling some data in:





And executing this code:



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:

Thank you for the worning. I don't use MySQL for this project but I'll have it in mind.

Its also a problem on MSSQL 2000 i assume and in fewer cases 2005, its a bit hard to debug. A coworker of mine learned this the hard way ;-)