Nhibernate queries with join (or fetch) return duplicates

Question

I meet unexpected behavior and It's not clear for me. Of course I can use distinct, but what is a reason?

I have entities (fluent auto-mapping):

public class Ticket
{
    public virtual int Id { get; set; }
    public virtual string Title { get; set; }
    public virtual IList<Activity> Activities { get; set; }
}

public class Activity
{
    public virtual int Id { get; set; }
    public virtual string Title { get; set; }
    public virtual Ticket Ticket { get; set; }
}

Test data (1 ticket with 5 activities):

new Ticket { Id = 1, Activities = new List<Activity>
    {
        new Activity(), new Activity(), new Activity(), new Activity()
    };

The query:

var report = GetSessionFactory()
    .OpenSession()
    .QueryOver<Ticket>()
    .JoinAlias(ticket => ticket.Activities, () => activity)
    .List<Ticket>();

And I have the following result:

enter image description here

Mr Mush · Accepted Answer · 2013-07-16 14:54:47Z

4

As you stand you are returning a cartesian product as you are joining a one-to-many table, in your case 1 x 5 rows. So if you want to go down this route then you will need to add .TransformUsing(Transformers.DistinctRootEntity)

Are you sure you don't want to load an activity and use the goodness of lazy loading to retrieve the Activities? In most cases this might be the more efficient way.

Somehing like:-

var ticket = session.QueryOver<Ticket>.Where(w => w.Id == id).SingleOrDefault();
OR
var ticket = session.Get<Ticket>(1);

then you can just call simply

foreach(var activity in  ticket.Activities)
{
 // do something here....
}

edited Jul 16, 2013 at 14:54

Mr Mush

1,5383 gold badges25 silver badges39 bronze badges

answered Feb 1, 2013 at 16:57

Rippo

22.5k14 gold badges83 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ievgen Over a year ago

I see. But what is a reason? Why NHibernate does not do it implicitly?

Rippo Over a year ago

Pass. That's a question for the nh development team. To be honest lazy loading is your friend.

Alexey Zimarev Over a year ago

What is suggested is leading to SELECT N+1

Rippo Over a year ago

make sure you have batch-size enabled and this does not lead to SELECT N+1

Collectives™ on Stack Overflow

Nhibernate queries with join (or fetch) return duplicates

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related