NHibernate - AddEntity and AddJoin problem

Question

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

I have an Item object which references a User object (to indicate the owner of the Item)

class Item
{
public User User;
}

My SQL query is (it's actually more complicated, which is why I can't use HQL, but I started with this to make sure the AddJoin/AddEntity was working):

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 item in the array contains an Item object (with the User property initialized) and the User object itself.

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

Answer 1

I just wasted an afternoon to figure this out. SetResultTransformer(CriteriaUtil.DistinctRootEntity) operates on the last thing added.

Here is what I did to get the first entity from the query. One catch though, I had to modify the NHibernate.DistinctRootEntityResultTransformer.TransformTuple() to be virtual. Not a big deal for us, because we have already branched NHibernate for some trivial additions like this. If you don't want to branch NH, it would be easy to roll your own IResultTransformer, making sure the items are unique.

add this to your query:

query.SetResultTransformer(new FirstTupleDistinctResultTransformer());

and this is the 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 item

Answer 3

It's been a while, but I think you're missing this:

.SetResultTransformer(new DistinctEntityRootTransformer())

Answer 4

try this

    using NHibernate.Transform;
    //..

    var x = session.CreateSQLQuery(sql)     
    .AddEntity("i", typeof(Item))     
    .AddJoin("u", "i.User") 
    .AddEntity("i", typeof(Item))
    .SetResultTransformer(new DistinctRootEntityResultTransformer())

    .List();