CreateCriteria over a collection of simple types

Question

How can I query using CreateCriteria over a collection of simple types?

For example, I have 1 class

public class Test
{
    public virtual Guid Id { get; set; }

    private ICollection<int> _values = new HashedSet<int>();        
    public virtual ICollection<int> Values
    {
        get { return _values; }
    }
}

And 2 tables to support it:

• Test with only 1 column: Id
• Values with 2 columns: TestId and Value

My goal is to rewrite the following query using CreateCriteria:

select * from test t
inner join values v on v.TestId = t.Id
where v.Value = 10

I have tried this:

Session.CreateCriteria<Test>("test")
  .CreateAlias("test.Values", "values")
  .Add(Restrictions.Eq("values", 10))
  .List();

Like I would done it with a collection of objects and, obviously, failed. The join is correct but how to add a restriction?

Answer 1

Despite of the question, I would suggest to use IList<ValueEntity> rather then IList<int>.

Well, that would be working only if the table containing the values ([dbo].[values]) could have its own surrogated ID column. I.e. with these columns: ID, TestId, Value we could introduce new entity ValueEntity and map it as first level citizens. Querying it then would become much more simple, including subqueries..

But I know that this is not a question here.

And in fact, there is a solution, there is answer to the above question. But please, at least try to consider the above suggestion...

NHibernate solution for IList<int> collection querying is:

Session.CreateCriteria<Test>("test")
  .CreateAlias("test.Values", "values")
  // we add magic keyword ".elements" here
  .Add(Restrictions.Eq("values.elements", 10))
  .List();

See the ".elements" which is a bit inproperly documented here. Also provided an answer to this similar quetion: NHibernate How do I query against an IList<string> property?