NHibernate query: string contained in either parent or child property

Question

I'm tearing my hair out on this one...

Suppose we have a list of blogs which have tags assigned to them. The user searches for any term, and the result must include:

1. All blogs whose name contains the term OR
2. All blogs where any tags name contains the term
3. Only the tags whose name matches the term

The SQL-query that achieves this:

declare @query varchar(20) = '%name%';
SELECT *
 FROM Blogs blog
INNER join BlogTags bt on blog.ID = bt.BlogId --join table
INNER join Tags tag on tag.Id = bt.TagId
                   and tag.name like @query -- condition 3
WHERE blog.name like @query -- condition 1
   OR tag.name like @query -- condition 2

I've been trying all day to make this work in an NHibernate query, but I'm always stuck on the OR condition since they don't refer to the same object. Using Restrictions.Like doesn't seem to understand that I want a nested property and filtering a nested object is typically done using CreateCriteria which is not available inside a Restrictions.Or. I also haven't found a way to incorporate condition 3.

I don't really care if it's done using Query, QueryOver or CreateCriteria. Currently, I just want it to work :)

---

Possibly relevant other SO questions:

• Filtering children
  • Answer: Session.CreateCriteria<Parent>().CreateCriteria("Children").Add(/*...*/)
• Restrictions.Or
• String.Contains in NHibernate
  • Answer: Session.QueryOver<Blog>().WhereRestrictionOn(c => c.Name).IsLike("")

Answer 1

Regardless of my comment above the following query should do the trick (the trick being the use of Aliases). I'm assuming that you have a class Blog which has a property List<Tag> Tags.

Blog bAlias = null;
Tag tAlias = null;

var blogs = session.QueryOver<Blog>(() => bAlias)
  .JoinAlias(() => bAlias.Tags, () => tAlias)
  .WhereRestrictionOn(_ => tAlias.Name).IsLike("%name%") // condition 3
  .Where(Restrictions.Disjunction()
     .Add(Restrictions.On<Blog>(_ => bAlias.Name).IsLike("%name%")) // condition 1
     .Add(Restrictions.On<Tag>(_ => tAlias.Name).IsLike("%name%")) // condition 2
  )
  .List<Blog>();

However, as in my comment above, condition 3 does not really make much sense here, unless you change the inner join to a left outer join.