NHibernate: Query filtering on a list of values using criteria

Question

I'm trying to filter by a list of values using the criteria API. I suspect that this is not possible, I'm just asking here to be sure.

class Entity
{
  int id { get; set; }
  IList<Guid> Guids { get; set; }
}

The mapping:

<class name="Entity">
  <id ...></id>
  <bag name="Guids" table="Entity_Guids">
    <key column="Entity_FK"/>
    <element column="Guid"/>
  </bag>
</class>

Assumed I have a list of Guids (actually these is another subquery). I want to filter all Entities where at least one guid is in the list of Guids.

Sql would look like this:

SELECT * 
FROM Entity e 
  inner join Entity_Guids eg 
    on  e.id = eg.Entity_FK
WHERE 
  eg.Guid in (subquery)

With Criteria API, this seems to be impossible.

ICriteria query = session
  .CreateCriteria(typeof(Entity), "e")
  .Add(Subqueries.In("e.Guids", subquery))

Throws an exception.

Answer 1

Your query will not work because the e.Guids property passed to the subquery is not a single value. To do it this way you are actually trying to perform an intersection and check that that intersection is not empty, which unfortunately does not exist in the criteria api, although you could probably do this with Linq.

You could probably still do this if your Guids were entities with the appropriate properties (the Value property contains the actual Guid) and there was a bidirectional relationship:

var subquery2 = DetachedCriteria.For<GuidEntity>()
  .Add(Subqueries.In("Value", subquery))
  .SetProjection("Entity_FK");

ICriteria query = session.CreateCriteria(typeof (Entity))
  .Add(Subqueries.In("Id", subquery2));