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.