Querying UserType's in NHibernate

I have the following scenario:

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.

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.

I'm creating a SelectedCatories type that's simply an IEnumerable, and my Product class looks like this:

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; }
}

I then created a SelectedCategoriesUserType class like so:

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(
                    开发者_JS百科stringCategory => categories.Single(cat => 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 => 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; }
    }
}

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|".

Right now, my naive implementation that barely makes my test pass looks like this:

    public IEnumerable<Product> 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<Product>();
        }
    }

My question is: what's the proper way to right that query?

A different way to do that ICriteria query would be this...

return Session
    .CreateCriteria(typeof(Product), "product")
    .Add(Expression.Sql(
        "{alias}.categories LIKE ?",
        string.Format("%|{0}|%", category.Id),
        NHibernateUtil.String))
    .List<Product>();

However, you may want to think about setting up a many-to-many table between Product and Category and setting up a collection of Categories in the Product class. You can still keep your field of concatenated Category Ids (I assume it's needed for legacy purposes), but tie it to the collection with something like this.

public virtual ISet<Category> Categories { get; private set; }

public virtual string CategoriesString
{
    get { return string.Join("|", Categories.Select(c => c.Id.ToString()).ToArray()); }
}

Doing something like this will let you set foreign keys on your tables, and make the queries a bit easier to construct.

return Session
    .CreateCriteria(typeof(Product), "product")
    .CreateCriteria("product.Categories", "category")
    .Add(Restrictions.Eq("category.Id", category.Id))
    .List<Product>();