queryover - QueryOver with Property and Property of Reference in Same Disjunction

Question

Alright, long time listener, first time caller.

I'm trying to write a query using the QueryOver API that will search for a number of terms across certain properties of my entity. The problem is that some of those properties are References to another entity; however, I still need to search for the given term in a property of that reference.

I'd like to be able to write something like the code below. In the code below, FirstName, MiddleName, and LastName are all basic string properties on the Person entity. The Department property is a reference property of type Department. The Department entity has three properties: Id, Value, Description. It is Person.Department.Value that must also be searched for the given term along with the basic properties.

var queryOver = session.QueryOver<Person>();
foreach (string term in searchTerms)
{
    queryOver = queryOver.Where(Restrictions.On<Person>(x => x.FirstName).IsInsensitiveLike(term, MatchMode.Anywhere) ||
                    Restrictions.On<Person>(x => x.LastName).IsInsensitiveLike(term, MatchMode.Anywhere) ||
                    Restrictions.On<Person>(x => x.MiddleName).IsInsensitiveLike(term, MatchMode.Anywhere) ||
                    //This following line doesn't work.
                    Restrictions.On<Person>(x => x.Department.Value).IsInsensitiveLike(term, MatchMode.Anywhere)
}

I have tried using aliases and using the NHibernate.Linq/Query API. I know I need to do some sort of join using an alias but I guess I'm not understanding all the questions/articles I've read. Most of them seem to talk about doing a query/subquery against the Reference or Child, but I'm having issues translating those queries into a query along side other properties and ORing them all together.

The final SQL I'm shooting for is something like this:

SELECT * 
FROM `persons` 
WHERE (`FirstName` LIKE '%term1%' OR 
       `LastName` LIKE '%term1%' OR
       `MiddleName` LIKE '%term1%' OR
       `Department`.`Value` LIKE '%term1%') AND //I know this won't work
      (`FirstName` LIKE '%term2%' OR 
       `LastName` LIKE '%term2%' OR
       `MiddleName` LIKE '%term2%' OR
       `Department`.`Value` LIKE '%term2%') AND etc...

Finally, thank you very much in advance for any help. I'm open to using a different API within NHibernate or even a different search stratedgy if you think I can achieve the same results with a different query.

score 0 · Accepted Answer · edited May 23 '17 at 12:23

Alright, I finally figured it out. Thanks to these posts for pointing me in the right direction:

QueryOver Or with Subquery

Filtering and projecting an association using NHibernate QueryOver

Here's the code that I came up with to do what I needed. I really didn't want to use ugly aliases with random variables set to null and I also didn't want to have a bunch of different queries all defined elsewhere. I can explain further if someone needs it.

var persons = session.QueryOver<Person>()
                .Where(Restrictions.Disjunction()
                    .Add(Subqueries.WhereProperty<Person>(x => x.Department.Id).In(QueryOver.Of<Department>().WhereRestrictionOn(x => x.Value).IsInsensitiveLike("somedep", MatchMode.Anywhere).Select(x => x.Id)))
                    .Add<Person>(x => x.LastName.IsInsensitiveLike("somedep", MatchMode.Anywhere)))
                .Where(Restrictions.Disjunction()
                    .Add(Subqueries.WhereProperty<Person>(x => x.Department.Id).In(QueryOver.Of<Department>().WhereRestrictionOn(x => x.Value).IsInsensitiveLike("myname", MatchMode.Anywhere).Select(x => x.Id)))
                    .Add<Person>(x => x.LastName.IsInsensitiveLike("myname", MatchMode.Anywhere))))
                .List();

queryover - QueryOver with Property and Property of Reference in Same Disjunction

1 Answers1