Creating a "where in" HQL query

Question

I am having trouble writing a HQL query which uses a "where in" clause.

Simplified classes look like this:

class Parent
{
    public virtual Int64 Id { get; private set; }
    public virtual string Name { get; private set; }
}

class Child
{
    public virtual Int64 Id { get; private set; }
    public virtual string Name { get; private set; }
    public virtual Parent Parent { get; private set; }
}

With mapping defined like this:

class ParentMap : ClassMap<Parent>
{
    Id(x => x.Id);
    Map(x => x.Name);
}

class ChildMap : ClassMap<Child>
{
    Id(x => x.Id);
    Map(x => x.Name);
    References(x => x.Parent);
}

I wanted to get Child instances which belong to certain Parent items, so I wrote the following code:

// get children for several parents (a flattened list)
IEnumerable<Parent> parents = GetParents();

// use hql
IQuery q = Session.CreateQuery("from Child as c where c.Parent in (:parents)");
q.SetParameter("parents", parents);

but the problem is, I am getting the following exception at q.SetParameter:

Test method Can_get_children_for_many_parents threw exception:
  NHibernate.PropertyAccessException:
  Exception occurred getter of Some.Namespace.Parent.Id 
  ---> System.Reflection.TargetException: Object does not match target type.

[Edit]

I tried using q.SetParameter("parents", parents.Select(p => p.Id);, but I get the same exception.

score 6 · Accepted Answer · edited Aug 26 '11 at 12:29

6

Try with something like this:

q.SetParameterList("parents", parents.ToList());

edited Aug 26 '11 at 12:29

Groo

45,930
15
109
179

answered Aug 26 '11 at 12:22

Iridio

9,073
3
44
69

+1, thanks, I just found it in [this thread](http://stackoverflow.com/questions/570229/hibernate-hql-query-how-to-set-a-collection-as-a-named-parameter-of-a-query). `parents` is of type `IEnumerable` in my case. – Groo Aug 26 '11 at 12:23
+1 for `SetParameterList`, but why `parents.Split` and not `parents.ToArray()`?? – Stefan Steinegger Aug 26 '11 at 12:28
@stefan: that's a portion of code I used. I guess the result is the same (assuming the data is always correct) – Iridio Aug 26 '11 at 12:31

Creating a "where in" HQL query

1 Answers1