Storing a Set of Integer values in Sql Server 2005 with nHibernate?

Question

What is the best way to store and map an Entity which contains a set of Integer as one of its attributes? I am using nHibernate over Sql Server 2005.

Will creating a CLR Custom Type will help here? If yes how it can be mapped using nHibernate?

I should be able to query on the Set. E.g.

select myEntity from MyEntities where myEntity.Integers = SetOf(2, 4, 5)

Answer 1

Assuming this class

class MyEntity
{
  //...
  public IList<int> Integers { get; private set; }
}

Just map it as a set.

<class name="MyEntity">
  <!-- ... -->
  <set name="Integers" table="MyEntity_Integers">
    <key column="MyEntity_FK"/>
    <element type="Int32" column="Value"/>
  </set>
</class>

You could try to filter the collection by:

from MyEntity e 
where e.Integers in (:set) 
  and size(e.Integers) = :setSize

This is probably not very fast.

---

A completely different approach: store the integers in some serialized form into a single text field.

You could write your own NHibernate custom type. Sort the integers before storing. You could store them in a format like "2;45;78;898". Filtering will be very fast, because it just matches the string. Changing the collection in the database could get hard. Another problem is that the column length is limited.

Here is an example of a NHibernate user type implementation.