CODEWITHSUNDEEP
nhibernate
Mark Sherretta
Mark Sherretta

Reputation: 10230

NHibernate - AddEntity and AddJoin problem

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).

Upvotes: 6

Views: 9648

Answers (6)

Piyush Aghera
Piyush Aghera

Reputation: 965

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

Upvotes: 3

Uwe Mahlberg
Uwe Mahlberg

Reputation: 21

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();

Upvotes: 1

Trent
Trent

Reputation: 2182

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];
    }
}

Upvotes: 5

David M
David M

Reputation: 72890

If your mapping is right you can just use a join fetch to get this all in a single hit like so:

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

Upvotes: 0

Ben Scheirman
Ben Scheirman

Reputation: 40961

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

.SetResultTransformer(new DistinctEntityRootTransformer())

Upvotes: 1

Frederik Gheysels
Frederik Gheysels

Reputation: 56934

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

Upvotes: 0

Related Questions