Parameter lists in NHibernate

Occasionally I need to return a set of entites that match a collection of parameters.  In SQL, I would use the “IN” clause, then manually create each parameter in ADO.NET.  With NHibernate, that’s not necessary anymore.  NHibernate has built-in capabilities for a collection parameter, creating all the necessary ADO.NET parameters behind the scenes.

For example, suppose my database has the following structure (from Northwind):

What I’m trying to do is to load up all Products for a certain set of Categories.  My Product and Category class are nothing interesting:

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Category Category { get; set; }
}

I didn’t bother mapping all of the properties, just the ones for the classes defined above.  Here are the mapping files:

<class name="Category" table="Categories">
    <id name="Id" column="CategoryID" type="int">
        <generator class="assigned" />
    </id>
    <property name="Name" not-null="true" column="CategoryName" />
</class>

<class name="Product" table="Products">
    <id name="Id" column="ProductId" type="int">
        <generator class="assigned" />
    </id>
    <property name="Name" not-null="true" column="ProductName" />
    <many-to-one name="Category" column="CategoryID">
    </many-to-one>
</class>

When using plain HQL, I need to use the SetParameterList method on the IQuery object:

[Test]
public void Should_get_products_by_categories_correctly_using_HQL()
{
    ISession session = GetSession();
    string hql = @"from Product p
                   where p.Category in (:categories)";

    var categoriesToSearch = new[] {new Category {Id = 1}, new Category {Id = 2}};

    var query = session.CreateQuery(hql);
    query.SetParameterList("categories", categoriesToSearch);

    var products = query.List<Product>();

    products.Count.ShouldEqual(24);
}

When using the Criteria API, I’ll need to use the InExpression:

[Test]
public void Should_get_products_by_categories_correctly_using_criteria()
{
    ISession session = GetSession();
    var criteria = session.CreateCriteria(typeof(Product));

    var categoriesToSearch = new[] {new Category {Id = 1}, new Category {Id = 2}};
    criteria.Add(new InExpression("Category", categoriesToSearch));
    var products = criteria.List<Product>();

    products.Count.ShouldEqual(24);
}

When I execute both of these, NHibernate produces the correct IN expression for each.  Unfortunately, I am ashamed to say I initially tried to create the parameter list myself.  Next time, I’ll know better.