Querying UserType's in NHibernate -

i have following scenario:

let's "product" table in legacy database has "categories" column of type string. column stores category id's separated sort of ascii character. instance: "|1|" (for category 1), "|1|2|3|" (for categories 1, 2, , 3), etc.

instead of exposing string property that, want expose ienumerable, users of product class don't have worry parsing values.

i'm creating selectedcatories type that's ienumerable, , product class looks 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 created selectedcategoriesusertype class so:

public class seletedcategoriesusertype : iusertype {     static readonly sqltype[] _sqltypes = {nhibernateutil.string.sqltype};      public bool equals(object x, object y)     {         // fix check 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 => 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     {         { return _sqltypes; }     }      public type returnedtype     {         { return typeof (selectedcategories); }     }      public bool ismutable     {         { return false; }     } } 

i want build query gives me product belongs in specific category (say, category 2), matching both "|2|", , "|1|2|3|".

right now, naive implementation barely makes test pass looks this:

    public ienumerable<product> getbycategory(category category)     {         using (isession session = nhibernatehelper.opensession())         {             return session                 .createsqlquery("select * product categories :category")                 .addentity(typeof(product))                 .setstring("category", string.format("%|{0}|%", category.id))                 .list()                 .cast<product>();         }     } 

my question is: what's proper way right query?

a different way icriteria query this...

return session     .createcriteria(typeof(product), "product")     .add(expression.sql(         "{alias}.categories ?",         string.format("%|{0}|%", category.id),         nhibernateutil.string))     .list<product>(); 

however, may want think setting many-to-many table between product , category , setting collection of categories in product class. can still keep field of concatenated category ids (i assume it's needed legacy purposes), tie collection this.

public virtual iset<category> categories { get; private set; }  public virtual string categoriesstring {     { return string.join("|", categories.select(c => c.id.tostring()).toarray()); } } 

doing let set foreign keys on tables, , make queries bit easier construct.

return session     .createcriteria(typeof(product), "product")     .createcriteria("product.categories", "category")     .add(restrictions.eq("category.id", category.id))     .list<product>();