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.

Yes, the subquery is a DetachedCriteria. The exception is that the argument 'key' must not be null. It seems to be an exception when accessing a dictionary and does not mean much. — Stefan Steinegger
– Stefan Steinegger, Commented Jun 28, 2009 at 20:30

Nigel · Accepted Answer · 2009-07-01 11:32:16Z

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));

answered Jul 1, 2009 at 11:32

Nigel

2,1603 gold badges20 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jan Zahradník Over a year ago

I think for correct behaviour you have to use Subqueries.PropertyIn() method which use "Id" as table column (ex. eg.Id in (select...)). Subqueries.In() check "Id" as constant (ex. 'Id' in (select...).

Collectives™ on Stack Overflow

NHibernate: Query filtering on a list of values using criteria

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related