Tuesday, March 29, 2011

NHibernate FAQ: how to write IN predicate / restriction to limit the number of rows affected by statement

A WHERE clause in SQL specifies that a SQL Data Manipulation Language (DML) statement should only affect rows that meet specified criteria. The criteria are expressed in the form of predicates. WHERE clauses are not mandatory clauses of SQL DML statements, but should be used to limit the number of rows affected by a SQL DML statement or returned by a query.
* wikipedia.org (excerpt from Wikipedia article)

IN is one of predicates used in SQL WHERE clause.

IN predicate

IN will find any values existing in a set of candidates.
SELECT ename WHERE ename IN ('value1', 'value2', ...)
All rows match the predicate if their value is one of the candidate set of values. This is the same behavior as
SELECT ename WHERE ename='value1' OR ename='value2'
except that the latter could allow comparison of several columns, which each IN clause does not. For a larger number of candidates, IN is less verbose.
* wikipedia.org (excerpt from Wikipedia article)

using IN (predicate) restriction in NHibernte

I will create query for the following table:
(I will use PostgreSQL and NHibernte version 3.1.0.GA for this example)
CREATE TABLE post
(
  id integer NOT NULL,
  title character varying(255),
  "text" character varying(255),
  updated timestamp without time zone,
  CONSTRAINT post_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE post OWNER TO postgres;

Now we will create mapping file:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true">
  <class name="Tests.Blog.Post, Tests" lazy="false">
    <id name="Id" access="field.camelcase-underscore" column="id" type="int">
      <generator class="native"/>
    </id>
 
    <property name="Title" access="field.camelcase-underscore" column="title" type="string" />
    <property name="Text" access="field.camelcase-underscore" column="text" type="string" />
    <property name="Updated" access="field.camelcase-underscore" column="updated" type="DateTime" />  

  </class>
</hibernate-mapping>

An here is my POCO class:
using System;

namespace Tests.Blog
{
  public class Post
  {
    private int _id = 0;
    private string _title = null;
    private string _text = null;
    private DateTime _updated = DateTime.Now;
  
    public Post ()
    {
    }
  
    public int Id
    {
      get { return _id; }
      private set { _id = value; }
    }
  
    public string Title
    {
      get { return _title; }
      set { _title = value; }
    }
  
    public string Text
    {
      get { return _text; }
      set { _text = value; }
    }
  
    public DateTime Updated
    {
      get { return _updated; }
      set { _updated = value; }
    }
  }
}

Here is code for in predicate using Criteria API:
  int[] keys = { 1, 5, 10 };
  IList<Post> posts = null;
  using (ISession session = OpenSession())
  using (ITransaction tx = session.BeginTransaction())
  {
    posts = session.CreateCriteria<Post>()
              .Add(Expression.In("Id", keys))
              .List<Post>();

    tx.Commit();
  }

  Console.WriteLine("Selected posts:");
  foreach(Post post in posts)
  {
    Console.WriteLine("Id: " + post.Id +
          " Title: " + post.Title +
          " Text: " + post.Text +
          " Updated: " + post.Updated);
  }

And here is code for in predicate using QueryOver:
  int[] keys = { 1, 5, 10 };
  IList<Post> posts = null;
  using (ISession session = OpenSession())
  using (ITransaction tx = session.BeginTransaction())
  {
    posts = session.QueryOver<Post>()
              .WhereRestrictionOn(p => p.Id).IsIn(keys)
              .List<Post>();
    
    tx.Commit();
  }

  Console.WriteLine("Selected posts:");
  foreach(Post post in posts)
  {
    Console.WriteLine("Id: " + post.Id +
          " Title: " + post.Title +
          " Text: " + post.Text +
          " Updated: " + post.Updated);
  }

And here is code for in predicate using Linq
  int[] keys = { 1, 5, 10 };
  IList<Post> posts = null;
  using (ISession session = OpenSession())
  using (ITransaction tx = session.BeginTransaction())
  {
    posts = (from p in session.Query<Post>() 
            where keys.Contains(p.Id) 
            select p)
            .ToList<Post>();
    
    tx.Commit();
  }

  Console.WriteLine("Selected posts:");
  foreach(Post post in posts)
  {
    Console.WriteLine("Id: " + post.Id +
          " Title: " + post.Title +
          " Text: " + post.Text +
          " Updated: " + post.Updated);
  }

Thanks for reading, hope my short example was helpful for you!