Filter nested models on property in last node with NHibernate

Question

I am using NHibernate with mapping by code.

I have three models: Solution, Installation and System. There are one-to-many relations between them. So that each Solution has a list of Installations, and each Installation has a list of Systems.

Each system has a property "Type", which can be "1" or "0".

I am trying to write a method in the Solution repository that will return all the Solutions, with their Installations with only the Systems of type "1".

I have tried the Where-keyword in the SystemMap but i get the same result with and without it. Then i tried a few different experiments with QueryOver(???) without success.

How do i go about to filter on information in the last node?

Thank to your answer, i have done the following implementation, but it results in a huge amount of Systems and Solutions. Maybe i have done something wrong?

The Maps are as follows:

    public SAPSolutionMap()
    {
        Id(t => t.YPID);

        Property(e => e.ShortName);
        Property(e => e.FullName);

        Bag(x => x.SapInstallations, colmap =>
        {
            colmap.Table("SAPInstallation");
            colmap.Key(x => x.Column("Solution"));
            colmap.Inverse(true);
            colmap.Lazy(CollectionLazy.NoLazy);
            colmap.Fetch(CollectionFetchMode.Join);
            colmap.Cascade(Cascade.None);
        }, map => map.OneToMany(m => m.Class(typeof(SAPInstallation))));
    }

    public SAPInstallationMap()
    {
        Id(t => t.InstallationNumber);

        Bag(x => x.SapSystems, colmap =>
        {
            colmap.Table("sapgui");
            colmap.Key(x => x.Column("Installation"));
            colmap.Inverse(true);
            colmap.Lazy(CollectionLazy.NoLazy);
            colmap.Cascade(Cascade.None);
            colmap.Fetch(CollectionFetchMode.Join);
            //colmap.Where("Type = 1");
        }, map => map.OneToMany(m => m.Class(typeof(SAPSystem))));

        ManyToOne(x => x.SapSolution, map =>
        {
            map.Column("Solution");
            map.NotNullable(true);
            map.Cascade(Cascade.None);
            map.Class(typeof(SAPSolution));
        });
    }

    public SAPSystemMap()
    {
        Id(t => t.ID, t => t.Generator(Generators.Identity));
        Property(e => e.Type);
        Property(e => e.ExplanationText);

        ManyToOne(x => x.SapInstallation, map =>
        {
            map.Column("Installation");
            map.NotNullable(true);
            map.Cascade(Cascade.None);
            map.Class(typeof(SAPInstallation));
        });
    }

And the Query:

    public IList<SAPSolution> GetProductionSystems()
    {
        SAPSystem syst = null;
        SAPInstallation installation = null;
        var subquery = QueryOver.Of(() => syst)
            .JoinQueryOver(x => x.SapInstallation, () => installation)
            .Where(() => syst.Type == 1)
            .Select(x => installation.SapSolution.YPID);

        // main Query 
        var query = Session.QueryOver<SAPSolution>()
            .WithSubquery
                .WhereProperty(root => root.YPID)
            .In(subquery);

        return query.List<SAPSolution>();
    } 

Thank you!

Answer 1

General solution should be:

// this is a subquery (SELECT ....
System syst = null;
Installation installation = null;
var subquery = QueryOver.Of(() => syst)
    .JoinQueryOver(x => x.Installation, () => installation)
    .Where(() => syst.Type == 1)
    .Select(x => installation.Solution.ID)
;

// main Query 
var query = session.QueryOver<Solution>()    
    .WithSubquery
        .WhereProperty(root => root.ID)
    .In(subquery)
    ;

var list = query
   .Take(10)
   .Skip(10)
   .List<Solution>();

What we can see, that Solution, Installation and System

• System has property Installation (many-to-one)
• Installation has property Solution (many-to-one)

This is expect-able, because it goes side by side with one-to-many (it is the reverse mapping)

So, then we create subquery, which returns just solution ID's which belong to system with searched Type.

Main query is flat (the great benefit) and we can use paging on top of it.

We would be able to do that even if there is only one way (one-to-many). But that will generate more complicated SQL query ... and does not make sense. In C# we can have both relations...

EXTEND:

You did a great job. Your mapping and query is really cool. But there is one big but: LAZY is what we should/MUST use. Check this:

NHibernate is lazy, just live with it, by Ayende

So, our, collections cannot be FETCHING with a JOIN, because that will multiply the result (10 solutions * 100 installation * 10 systems == 10000 results)

Bag(x => x.SapSystems, colmap =>
{ 
    ...
    // THIS IS not good way
    colmap.Lazy(CollectionLazy.NoLazy);
    colmap.Fetch(CollectionFetchMode.Join);

We should use LAZY as possible. To avoid later 1 + N issue, we can use batch-fetching (for example check this)

• How to Eager Load Associations without duplication in NHibernate?

So, our collections should be mapped like this:

Bag(x => x.SapSystems, colmap =>
{ 
    ...
    // THIS IS not good way
    colmap.Lazy(CollectionLazy.Lazy);
    colmap.BatchSize(100);

With this setting, the query will really use only the root object and related collections will be loaded very effectively