title: Querying UserType's in NHibernate
body: <p>I have the following scenario:</p>
  
  <p>Let's say that my "Product" table in this legacy database has a "Categories" column of type string. This column stores the category ID's separated by some sort of ascii character. For instance: "|1|" (for category 1), "|1|2|3|" (for categories 1, 2, and 3), etc.</p>
  
  <p>Instead of exposing a string property for that, I want to expose an IEnumerable, so that users of my Product class don't have to worry about parsing those values.</p>
  
  <p>I'm creating a SelectedCatories type that's simply an IEnumerable, and my Product class looks like this:</p>
  
  <pre><code>public class Product
  {
      public virtual Guid Id { get; set; }
      public virtual string Name { get; set; }
      public virtual bool Discontinued { get; set; }
      public virtual SelectedCategories Categories { get; set; }
  }
  </code></pre>
  
  <p>I then created a SelectedCategoriesUserType class like so:</p>
  
  <pre><code>public class SeletedCategoriesUserType : IUserType
  {
      static readonly SqlType[] _sqlTypes = {NHibernateUtil.String.SqlType};
  
      public bool Equals(object x, object y)
      {
          // Fix this to check for Categories...
          if (ReferenceEquals(x, y)) return true;
          if (x == null || y == null) return false;
          return x.Equals(y);
      }
  
      public int GetHashCode(object x)
      {
          return x.GetHashCode();
      }
  
      public object NullSafeGet(IDataReader rs, string[] names, object owner)
      {
          object obj = NHibernateUtil.String.NullSafeGet(rs, names[0]);
          if (obj == null) return null;
  
          string[] stringCategories = obj.ToString().Split(new[] {'|'}, StringSplitOptions.RemoveEmptyEntries);
  
          var categories = new Categories();
  
          return
              new SelectedCategories(
                  stringCategories.Select(
                      stringCategory =&gt; categories.Single(cat =&gt; cat.Id == int.Parse(stringCategory)))
                      .ToList());
      }
  
      public void NullSafeSet(IDbCommand cmd, object value, int index)
      {
          if (value == null)
          {
              ((IDataParameter) cmd.Parameters[index]).Value = DBNull.Value;
          }
          else
          {
              var theCategories = (SelectedCategories) value;
  
              var builder = new StringBuilder();
              builder.Append("|");
              theCategories.ForEach(i =&gt; builder.AppendFormat("{0}|", i.Id.ToString()));
  
              ((IDataParameter) cmd.Parameters[index]).Value = builder.ToString();
          }
      }
  
      public object DeepCopy(object value)
      {
          return value;
      }
  
      public object Replace(object original, object target, object owner)
      {
          throw new NotImplementedException();
      }
  
      public object Assemble(object cached, object owner)
      {
          throw new NotImplementedException();
      }
  
      public object Disassemble(object value)
      {
          throw new NotImplementedException();
      }
  
      public SqlType[] SqlTypes
      {
          get { return _sqlTypes; }
      }
  
      public Type ReturnedType
      {
          get { return typeof (SelectedCategories); }
      }
  
      public bool IsMutable
      {
          get { return false; }
      }
  }
  </code></pre>
  
  <p>I then want to build a query that gives me back any product that belongs in a specific category (say, category 2), matching both "|2|", and "|1|2|3|".</p>
  
  <p>Right now, my naive implementation that barely makes my test pass looks like this:</p>
  
  <pre><code>    public IEnumerable&lt;Product&gt; GetByCategory(Category category)
      {
          using (ISession session = NHibernateHelper.OpenSession())
          {
              return session
                  .CreateSQLQuery("select * from product where categories LIKE :category")
                  .AddEntity(typeof(Product))
                  .SetString("category", string.Format("%|{0}|%", category.Id))
                  .List()
                  .Cast&lt;Product&gt;();
          }
      }
  </code></pre>
  
  <p>My question is: what's the proper way to right that query?</p>