NHibernate: Criteria / SubCriteria Projection

Question

I have a Mod class that has a member:

ICollection<Event> EventList 

The Event class has a member:

 public virtual EventType Type { get; set; } 

The EvenType class has a member:

  public virtual int Id 

I want to get a list of all events in a module that have an identifier of 1 or 2 using NHibernate criteria. I have done this:

 var subCriteria = DetachedCriteria.For<Mod>() .Add(Restrictions.In("Event.Type", new int[] {1, 2 }) ); criteria.Add(Subqueries.Exists(subCriteria)); 

but I get a runtime error saying I can’t use subqueries by criteria without projection.

So, great, but I do not know what to put for Projection. I can find examples of how to make projections, but nothing that really explains the purpose. I tried different things, but all of them lead to a runtime error:

Message: value cannot be null. Parameter Name: Key Source: mscorlib Reference Link: ParamName: key

I need to use a subquery because I will add more when it works.

Can you suggest what to do for Projection?

Answer 1

As described here: 16.4. Associations , this can be done as follows:

 IQueryOver<Mod, Event> query = session .QueryOver<Mod>() .JoinQueryOver<Event>(mod => mod.EventList) .WhereRestrictionOn(evnt => evnt.Type.Id).IsIn(new object[] { 1, 2}); var result = query.List<Mod>(); 

EDIT: pure criteria API:

 var criteria = session.CreateCriteria<Mod>(); var sub = criteria.CreateCriteria("EventList", JoinType.LeftOuterJoin); sub.Add(new InExpression("Type", new object[] { 1, 2 })); var result = criteria.List<Mod>(); 

using separate criteria. In this case, the EventType must have the ModId property or refer to the Mod instance. In the subquery, we must return a list of valid Mod.IDs

 var sub = DetachedCriteria .For<Event>() .Add(Restrictions.In("Type", new int[] {1, 2 })) // WHERE .SetProjection(Projections.Property("ModId")); // Mod.ID the SELECT clause var criteria = session.CreateCriteria<Mod>(); criteria.Add(Subqueries.PropertyIn("ID", sub)); // Mod.ID in (select var result = criteria.List<Mod>();