hql Session.CreateQuery() ; getting multiple records

ericfkaufman · **Posted:** Thu Dec 27, 2007 7:12 pm

Hey folks. I've got a query doing something like this:

hqlQuery = "Select cl from Client cl"
+ " left outer join cl.Contacts as con "
+ " where con.Contact.Name = 'Frank' "

Client's mapping file is a bag (many to many), as is Contact's (bag as well); neither side is marked as inverse. There is an intersect table in between them to support the many to many relationship, named ClientContact (has a ClientID, and a ContactID). One Contact can be shared amongst multiple Clients, and a Client can have multiple Contacts.

If I have one client that has two contacts named Frank, I get two clients back, just like how it would work if I used a join in SQL. Isn't Hibernate supposed to protect me from this? I'm sure it's me not using it correctly.

Thanks so much for the help ladies and gents.

marcal · **Posted:** Thu Dec 27, 2007 11:15 pm

Actually, no, this is the expected result. On your IQuery object, use

Code:

.SetResultTransformer( CriteriaUtil.DistinctRootEntity )

ericfkaufman · **Posted:** Fri Jan 04, 2008 2:54 pm

Thanks for the heads up. I think that's the way to do it, but I'm getting an exception at that point. I'm using version 1.2.0.3001, and according to this bug fix, it was released in 1.2.1:

http://jira.nhibernate.org/browse/NH-1018

The error is:

Unable to cast object of type 'YourLibrary.YourNameSpace.Type' to type 'System.Object[]'.

I've been so knee deep in 1.2.0 that I didn't even realize that 1.2.1 had come out. I'll upgrade and see if that resolves it.

ericfkaufman · **Posted:** Fri Jan 04, 2008 3:05 pm

Marcal;

Thanks so much for that help! I upgraded to 1.2.1, and everything is working like a champ now. My user interface developers are very happy now that they have the correct amount of objects! Reply marked as useful.