Optimizing Performance in NHibernate: Part 2: A Collection of Enhancements

using System;
using System.Collections.Generic;
using System.Text;

namespace Northwind.Domain
{
    class CategorySearchCriteria:ISearchCriteria
    {
        /// <summary>
        /// Generally, it is a better idea to store all of your default values in a more central location,
        /// but we will be storing them here for the sake of simplicity
        /// </summary>
        #region defaults
        private static readonly int defaultID = -1;
        private static readonly bool defaultExcludeCategoriesWithProducts = false;
        #endregion
        
        #region members
        private int id = defaultID;
        private bool excludeCategoriesWithProducts = defaultExcludeCategoriesWithProducts;
        #endregion

        #region properties
        /// <summary>
        /// Search for a specific Category ID
        /// </summary>
        public int Id
        {
            get { return id; }
            set { id = value; }
        }
        /// <summary>
        /// Use this to search only within the realm of categories without any products
        /// </summary>
        public bool ExcludeCategoriesWithProducts
        {
            get { return excludeCategoriesWithProducts; }
            set { excludeCategoriesWithProducts = value; }
        }

        #endregion

        #region HQL
        public string HQL
        {
            get
            {
                //select new Category(c.Id, c.CategoryName, count(p)) from Category c join c.Products p group by c.Id, c.CategoryName order by count(p)
                StringBuilder builder = new StringBuilder("select cat from Category cat where (1=1) ");
                if (id != defaultID)
                    builder.Append(" and cat.ID = '" + id + "'");
                if (excludeCategoriesWithProducts != defaultExcludeCategoriesWithProducts)
                    builder.Append(" and cat.Id not in (select p.Category.Id from Product p) ");
                return builder.ToString();
            }
        }
        #endregion
    }
}