NHibernate - problem with AddEntity and AddJoin

Question

I am using NHibernate with an SQL query to populate some entity objects.

I have an Item object that references a User object (specify the owner of the element)

class Item { public User User; } 

My SQL query (this is actually more complicated, so I can’t use HQL, but I started with this to make sure AddJoin / AddEntity works):

 SELECT {i.*}, {u.*} FROM Item i INNER JOIN User u ON (i.UserId = u.Id) WHere i.Id = 5 

Here is my code:

 var x = session.CreateSQLQuery(sql) .AddEntity("i", typeof(Item)) .AddJoin("u", "i.User") .List(); 

When I run this, I get a two-dimensional array. Each element of the array contains an Item object (with an initialized User property) and the User object itself.

What am I missing? I was hoping to get a list of Item objects with an initialized User property (that’s how I interpreted the documentation).

Answer 1

I just took a day to figure it out. SetResultTransformer (CriteriaUtil.DistinctRootEntity) works with the last addition added.

Here is what I did to get the first object from the request. One catch though, I had to modify NHibernate.DistinctRootEntityResultTransformer.TransformTuple () to be virtual. This is not very important for us, because we already have a forked NHibernate for some trivial add-ons like this one. If you don't want to bind NH, it would be easy to collapse your own IResultTransformer, making sure the elements are unique.

add this to your query:

 query.SetResultTransformer(new FirstTupleDistinctResultTransformer()); 

and this is a new class:

 public class FirstTupleDistinctResultTransformer : DistinctRootEntityResultTransformer { public override object TransformTuple(object[] tuple, string[] aliases) { return tuple[0]; } } 

Answer 2

 var x = session.CreateSQLQuery(sql) .AddEntity("i", typeof(Item)) .AddJoin("u", "i.User") .AddEntity("i", typeof(Item)).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); 

it will return only the item

Answer 3

It has been a while, but I think you are missing this:

 .SetResultTransformer(new DistinctEntityRootTransformer()) 

Answer 4

try it

  using NHibernate.Transform; //.. var x = session.CreateSQLQuery(sql) .AddEntity("i", typeof(Item)) .AddJoin("u", "i.User") .AddEntity("i", typeof(Item)) .SetResultTransformer(new DistinctRootEntityResultTransformer()) .List(); 

Answer 5

What happens if you omit the AddJoin method? Isn't it enough just to specify AddEntity?

Answer 6

If your mapping is correct, you can simply use the join fetch command to get it all in one beat:

 var x = session.CreateQuery("from Item i join fetch i.User").List<Item>();