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-06T04:44:15.883

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 06 '11 at 04:44

answered Dec 05 '11 at 08:51

Firo

30,626
4
55
94

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. – paligap Dec 05 '11 at 22:11
@paligap made it an Id compare – Firo Dec 06 '11 at 04:44
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. – paligap Dec 06 '11 at 22:33

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

1 Answers1