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.

Related Articles:

Post Footer automatically generated by Add Post Footer Plugin for wordpress.

About Jimmy Bogard

I'm a technical architect with Headspring in Austin, TX. I focus on DDD, distributed systems, and any other acronym-centric design/architecture/methodology. I created AutoMapper and am a co-author of the ASP.NET MVC in Action books.
This entry was posted in NHibernate. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Ray

    Oh boy… Jimmy you not the only one… Thanks for sharing!

  • Ted

    Can you use parameters in an in clause? Or does it just produce something like:
    SELECT * from Products
    WHERE CATEGORY IN (1,2)

  • http://jimmybogard.lostechies.com Jimmy Bogard

    @Ted

    No, it creates parameters for each value for you. I forgot to include the SQL generated in the post.

  • http://www.thefreakparade.com Nathan

    Any idea if there is a LINQ for NH syntax to accomplish the same thing. We’re humming along using LINQ but have to drop down to HQL for these pesky IN style queries.

  • http://jimmybogard.lostechies.com Jimmy Bogard

    @Nathan

    Try using the “join” LINQ clause. I’m not sure if the Linq to NH developers support this use, but I’ve seen examples for other IQueryProvider implementations.

  • chris

    I hate asking for help on blog posts but this is so close…

    From your example above, using the criteria API. How would I get a list of categories given a list of products.

    Thanks for your time!

  • chris

    Figured it out…

    needed to add a bag to the Categories
    use a CreateCritera and InExpression

    Thanks again!

  • http://www.lostechies.com/members/bogardj/default.aspx bogardj

    @chris

    Good, cause I was stumped!

  • Rich

    Just as a heads up, there seems to be some bugs using empty lists and null lists using the SetParameterList. If you change to use Criteria, then it’ll work fine.

  • http://jcallico.myopenid.com/ Javier Callico

    I as well was building the IN list myself until I found your article. It works great. The generated sql for a list containing two values looks like this:  ‘… where event0_.Code in (@p0 , @p1)’,@p0=N’Test’,@p1=N’Test2′