nhibernate QueryOver select one table after join

Question

So I am having issues selecting just the Parent entities as this will also return the Child entities. Currently I have the following entities:

public class Parent
{
    public virtual string KeyPart1 { get; set; }
    public virtual string KeyPart2 { get; set; }    
    public virtual string KeyPart3 { get; set; }
    public virtual string ParentProperty { get; set; }

    public virtual ISet<Child> Children { get; set; } = new HashSet<Child>();
}

public class Child
{
    public virtual string KeyPart1 { get; set; }
    public virtual string KeyPart2 { get; set; }    
    public virtual string KeyPart3 { get; set; }
    public virtual string KeyPart4 { get; set; }
    public virtual string ChildProperty { get; set; }
}

And the actual Query:

Parent parentAlias = null;
Child childAlias = null;
var query = Session.QueryOver(() => parentAlias)
    .Left.JoinAlias(x => x.Children, () => childAlias)
    .Where(whereClause);

This generates the a sql statement very similar to this:

SELECT
    this_.KeyPart1 as KeyPart11_5_4_,
    this_.KeyPart2 as KeyPart22_5_4_,
    this_.KeyPart3 as KeyPart33_5_4_,
    this_.ParentProperty as ParentProperty4_5_4_,
    childalias1_.KeyPart1 as KeyPart11_6_6_,
    childalias1_.KeyPart2 as KeyPart22_6_6_,
    childalias1_.KeyPart3 as KeyPart33_6_6_,
    childalias1_.KeyPart4 as KeyPart44_6_6_,
    childalias1_.ChildProperty as ChildProperty5_6_6_,
FROM
    Parent this_ 
left outer join
    Child childalias1_ 
        on this_.KeyPart1=childalias1_.KeyPart1 
        and this_.KeyPart2=childalias1_.KeyPart2 
        and this_.KeyPart3=childalias1_.KeyPart3 
WHERE
    (SomeWhereClause)

Notice that this will return both Parent and Child tables. When

query.List()

is run, it will retrieves all of the Parents + all of the children of each parent, creating duplicate parent entries in the end result list. I just want to retrieve all of the Parents or generate the a sql statement similar to the following:

SELECT
    this_.KeyPart1 as KeyPart11_5_4_,
    this_.KeyPart2 as KeyPart22_5_4_,
    this_.KeyPart3 as KeyPart33_5_4_,
    this_.ParentProperty as ParentProperty4_5_4_,
FROM
    Parent this_ 
left outer join
    Child childalias1_ 
        on this_.KeyPart1=childalias1_.KeyPart1 
        and this_.KeyPart2=childalias1_.KeyPart2 
        and this_.KeyPart3=childalias1_.KeyPart3 
WHERE
    (SomeWhereClause)

Does anyone have any tips on doing this using nhibernate's QueryOver API?

score 1 · Accepted Answer · answered Apr 05 '18 at 06:26

One way would be, to use Transformers.DistinctRootEntity For example:

var query = Session.QueryOver(() => parentAlias)
    .Left.JoinAlias(x => x.Children, () => childAlias)
    .Where(whereClause)

    // Here is the trick
    .TransformUsing(Transformers.DistinctRootEntity)

See more in this Andrew Whitaker post:

QueryOver Series - Part 4: Transforming

The other way...

Well, I would not go, in fact I do go, do not use this approach. My really proved way is to:

Create query from Child and join parent (star schema)
If we have to query parent
- for filtering through child use subselects
- for loading children in batches use mapping setting batch-size

The first is obvious, and because we query star schema, we can easily use paging

The second would need some adjustments for filtering parent by child:

Fetching Paginated Entity with Collection

For effective loading of child collection, we can profit from batch-size mapping. Check these:

Thanks that worked however it sill seems to creates the same query then nhibernate filters out the data after the it is retrieved. — Nico Pizzo, Apr 05 '18 at 15:12

nhibernate QueryOver select one table after join

1 Answers1

QueryOver Series - Part 4: Transforming