NHibernate Polymorphic Query on a Collection

Question

I'm trying to write a query in NHibernate. I don't really care if I use the Criteria API or HQL, I just can't figure out how to write the query.

Here's my model:

public class LogEntry { public DateTime TimeCreated { get; set; } }
public class Note : LogEntry { public string Content { get; set; } }

public class Workflow { public IList<LogEntry> Log { get; set; } }

I want the query to return all Workflows that which contain a Note with specific words in the Content of the note.

In pseudo-SQL, I'd write this like:

select w.*
from Workflow w
join w.Log l where l is class:Note
where (Note)l.Content like '%keyword%'

Answer 1

I don't know if there is a better way, but I use subqueries for this:

from Workflow w
  join w.Log l
where l in (
  select n 
  from Note n 
  where n.Content like '%keyword%'
)

(if this doesn't work, write l.id in (select n.id...)

In criteria, you can directly filter properties that are only available on a subclass, but you shouldn't, because it only filters for the first subtype it finds where this property is defined.

I use subqueries as well:

DetachedCriteria subquery = DetachedCriteria.For<Note>("n")
  .Add(Expression.Like("n.Content", "%keyword%"))
  .SetProjection(Projections.Property("n.id"));

IList<Workflow> workflows = session.CreateCriteria<Workflow>("w")
  .CreateCriteria("w.Log", "l")
  .Add(Subqueries.PropertyIn("l.id", subquery))
  .List<Workflow>();

Answer 2

I'm not sure about the Criteria API, but HQL seems to handle polymorphic queries quite well, even when searching on a property that only exists in a specific sub-class. I would expect the following to work:

from Workflow w join w.Log l where l.class = Note and l.Content like '%keyword%'