NHibernate: Custom Criterion to manipulate joins

Question

I have an odd requirement that my clients have imposed on me. They have certain queries which they have spent a lot of time optimizing that work as follows:

1. A stored procedure builds up an "id-list" which basically represents the "Where" filter
2. The id-list is joined into your data tables

The id-list would look something like this

IdListTable.Customers_Id

1087,
10094,
87,
1077

The joining query therefore looks like:

SELECT c.Id, c.FirstName, c.LastName
FROM Customers c INNER JOIN IdListTable idList ON (c.Id = idList.Customers_Id);

I would like to be able to do something like this in NHibernate

IEnumerable<Customer> GetMatching(Specification spec) {
  string idListName = "IdListTable";

  _idListGenerator.BuildIdList(idListName);

  return _session.CreateCriteria<Customer>().
    Add(new JoinIdListCriterion(idListName)
    .Enumerable<Customer>()
}

So first of all, is this the correct concept? Do I want to implement my own ICriterion or is that for something else altogether?

Secondly, how do I actually do this. I've tried implementing AbstractCriterion and reading the doc-comments and I'm just not sure where I would hook into the query building process.

Answer 1

Your best bet is to add the "id-list" table to your mappings, so you can perform the join just like any other table.

Another option is to perform the join on the WHERE clause, so you'll get something like:

SELECT c.Id, c.FirstName, c.LastName
FROM Customers c WHERE c.Id IN (SELECT Customers_Id FROM IdListTable);

You can make it work using a SqlCriteria:

return _session.CreateCriteria<Customer>("c")
  .Add(Expression.Sql("c.Id IN (SELECT Customers_Id FROM IdListTable)", new object[0], new IType[0]))
  .Enumerable<Customer>();