CODEWITHSUNDEEP
c#nhibernatequeryover
LIvanov
LIvanov

Reputation: 1336

NHibernate QueryOver Where Or on property and nested collection

I have classes Foo and Bar

class Foo {
    string Name;
    IList<Bar> Bars;
}

class Bar {
    string Name;
}

I have an input q and I want to write an NHibernate query that returns a list of Foo objects if Foo.Name contains q or any of the Bars objects' Names contain q.

I know how I can make separate queries but I am not sure how to combine it into 1 with the OR operator in between. Basically if a Foo object contains q in its name or in any of its Bars names, I want the Foo object to be in the output.

this.sessionFactory.GetCurrentSession()
    .QueryOver<Foo>()
    .Where(x => x.Name.IsInsensitiveLike(q))
    .List();

this.sessionFactory.GetCurrentSession()
    .QueryOver<Foo>()
    .JoinQueryOver<Bar>(x => x.Bars)
    .Where(x => x.Name.IsInsensitiveLike(q))
    .List();

Upvotes: 0

Views: 873

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123901

Let's start with declaring aliases, for later use in queries

Foo foo = null;
Bar bar = null;

The first approach now, is to join collection (and get a bit ugly results, with multiplied parent for each matching child)

.QueryOver<Foo>(() => foo)
.JoinQueryOver<Bar>(x => x.Occupations, () => bar)
.Where(x => foo.Name.IsInsensitiveLike(q)
         || bar.Name.IsInsensitiveLike(q))

This will work, but could return some rows repeating parent Foo, in case that there are more matching children Bar...

Foo1, Bar1
Foo1, Bar2

To get clean parent result-set only, we need to replace JOIN with subquery

.QueryOver<Foo>(() => foo)
.Where(Restrictions
    .Or(
        Restrictions.Where(() => foo.Name.IsInsensitiveLike(q)),
        Subqueries
            .WhereProperty(() => foo.ID)
                .In(QueryOver.Of<Bar>(() => bar)
                    .Where(() => bar.Name.IsInsensitiveLike(q))
                    .Select(y => bar.Foo.ID)
                )
    )
)

That also expects, that child Bar has back reference to Foo... which is absolutely legal and business domain model supporting.

Upvotes: 1

Related Questions