Finding the row with matching relations using HQL

Question

I am using Castle ActiveRecord and NHibernate.

I have an Instance class which has a many-to-many relationship with a Component class. I would like to find the instance which is related to a specific set of components. Is this possible in HQL (or anything else in NHibernate)?

The linq version of this function would be:

public Instance find(IEnumerable<Component> passed_components)
{
    return Instance.Queryable.Single(i => passed_components.All(x => i.Components.Contains(x)));
}

Of course the NHibernate linq implementation can't handle this.

I can write the HQL to do this for one of the components:

Instance.FindOne(new DetachedQuery("from Instance i where :comp in elements(i.Components)").SetParameter("comp", passed_components.First()));

But it looks like in only compares one item to a set, it can't compare a set to a set.

EDIT:

This is the best I could do:

IQueryable<Instance> q = Queryable;
foreach(var c in components) {
    q = q.Where(i => i.Components.Contains(c));
}

But this is very inefficient. It adds a subselect to the SQL query for every where clause. An unnecissarily long subselect at that. It joins the Instance table, the Instance/Component join table, and the Component table. It only needs the Instance/Component join table.

Because of the nature of my data, I am going to implement a hybrid solution. Narrow down the instances in the query, then use linq to objects to get the correct one if necessary. The code looks like this:

IQueryable<Instance> q = Queryable;
foreach(var c in components.Take(2)) {
    q = q.Where(i => i.Components.Contains(c));
}

var result = q.ToArray();
if(result.Length > 1) {
    return result.SingleOrDefault(i => !components.Except(i.Components).Any());
}
else return result.FirstOrDefault();

Anyone have a better way?

Answer 1

Using the NHibernate.Linq provider, the following should work:

var passed_components = new List<Component>();
var instance = session.Linq<Instance>()
                      .Where(i => !passed_components.Except(i.Components).Any())
                      .SingleOrDefault();

You can download the provider here and read more about it here and here.