NHibernate, Separate queries (using QueryOver) to populate complex object

Question

We're using a database that doesn't support query batching so we can't make use of NHibernate Futures in this instance. We know we can still populate our complex object graph using multiple-queries (to avoid Cartesian products) but need advice if I can refactor the approach.

Please note > Sole developer here, so seeking advice.

Here is some sample code which illustrates the current approach;

var fruitBasketAlias = new Store.FruitBasket(); 
var yoghurtAlias = new Store.Yoghurt();
var flavourAlias = new Store.Flavour();
var ownersAlias = new Store.Owner();

var FruitBaskets = session.QueryOver(() => fruitBasketAlias)
    .Where(() => fruitBasketAlias.Owner.ID == OwnerID
                    && fruitBasketAlias.ExpiryDate <= dateTO
                    && fruitBasketAlias.ExpiryDate >= dateFROM)
    .Fetch(x => x.BasketLiner).Eager
    .List();

session.QueryOver(() => fruitBasketAlias)
    .Select(x => x.Yoghurts)
    .Where(() => fruitBasketAlias.Owner.ID == OwnerID
                    && fruitBasketAlias.ExpiryDate <= dateTO
                    && fruitBasketAlias.ExpiryDate >= dateFROM)
    .JoinAlias(() => fruitBasketAlias.Yoghurts, () => yoghurtAlias, JoinType.LeftOuterJoin)
    .JoinAlias(() => yoghurtAlias.Flavour, () => flavourAlias, JoinType.InnerJoin)
    .List();

session.QueryOver(() => fruitBasketAlias)
    .Where(() => fruitBasketAlias.Owner.ID == OwnerID
                    && fruitBasketAlias.ExpiryDate <= dateTO
                    && fruitBasketAlias.ExpiryDate >= dateFROM)
    .JoinAlias(() => fruitBasketAlias.Yoghurt, () => yoghurtAlias, JoinType.LeftOuterJoin)
    .JoinAlias(() => yoghurtAlias.Flavour, () => flavourAlias, JoinType.InnerJoin)
    .JoinAlias(() => flavourAlias.Owners, () => ownersAlias, JoinType.LeftOuterJoin)
    .List();

You can see from the code above that I am using three separate queries to populate a list of FruitBaskets. This approach is working but I suspect there is a better way to join all the children into the parent object without having to query from the root object each time.

Is there an approach I can use which will enable me to apply the where condition to the parent object and use the results of that query to automatically obtain all the children objects. Please note that children can go 3 levels deep, i.e. FruitBasket.Yoghurt.Flavour.Owners.

Any advice is appreciated.

C# .NET 4, NHibernate 3.0

Firo · Accepted Answer · 2011-12-06 04:44:15Z

1

var FruitBaskets = session.QueryOver<FuitBasket>()
    .Where(b => b.Owner.ID == OwnerID
                    && b.ExpiryDate <= dateTO
                    && b.ExpiryDate >= dateFROM)
    .Fetch(x => x.BasketLiner).Eager
    .JoinAlias(b => b.Yoghurts, () => yoghurtAlias, JoinType.LeftOuterJoin)
    .List();

var yoghurts = session.QueryOver<Store.Yoghurt>()
    .WhereRestrictionOn(y => y.Id).In(FruitBaskets.SelectMany(b => b.Yoghurts).Select(y = > y.Id).Distinct())
    .JoinAlias(y => y.Flavour, () => flavourAlias, JoinType.InnerJoin)
    .List();

session.QueryOver<Store.Flavor>()
    .WhereRestrictionOn(f => f.Id).In(yoghurts.SelectMany(y => y.Flavors).Select(b = > f.Id).Distinct())
    .JoinAlias(f => f.Owners, () => ownersAlias, JoinType.LeftOuterJoin)
    .List();

couldn't test it though

edited Dec 6, 2011 at 4:44

answered Dec 5, 2011 at 8:51

Firo

30.8k4 gold badges57 silver badges97 bronze badges

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

3 Comments

paligap Over a year ago

Thank you Firo, I'am investigating your approach and the use of "WhereRestrictionOn" because I feel you're heading in the right direction. The code above doesn't work with a "cannot determine member for y" error.

Firo Over a year ago

@paligap made it an Id compare

paligap Over a year ago

Thanks Firo ... I was able to make a few changes and the result is much cleaner SQL which populates the object graph. Changing to an ID compare was key, thanks for pointing me in the right direction.

Collectives™ on Stack Overflow

NHibernate, Separate queries (using QueryOver) to populate complex object

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related