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?

score 1 · Accepted Answer · edited May 23 '17 at 12:13

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?

score 0 · Answer 2 · answered Nov 06 '14 at 00:09

The approach with elements didnt worked for me. Somehow NHibernate picked the wrong aliases... So from

CurrentSession.CreateCriteria<EmployeeIntranetSettings>("settings")
    .CreateAlias("settings.DefaultLMSOwnershipCapacities", "capacity")
    .Add(Restrictions.Eq("capacity.elements", OwnershipCapacity.VehicleFinance))
    .SetProjection(Projections.Id())
    .List<Guid>();

It created

SELECT this_.Id as y0_ 
FROM intranet.sfEmployeeIntranetSettings this_ 
inner join contact.sfEmployeeDefaultLMSOwnershipCapacities defaultlms3_ on this_.Id=defaultlms3_.EmployeeId
WHERE capacity1_.Capacity = @p0

As you can see - aliases for capacities are different.

So I would second the suggestion about the ValueType Entity despite the fact that it might be against DDD. And as a working solution with a current architecture (at least for me):

CurrentSession.CreateCriteria<Employee>("employee")
    .Add(Expression.Sql(
        "Exists (select top 1 null from contact.sfEmployeeDefaultLMSOwnershipCapacities dc where dc.EmployeeId = {alias}.Id and dc.Capacity = ?)", 
        "'" + OwnershipCapacity.VehicleFinance + "'", 
        NHibernateUtil.String))
    .SetProjection(Projections.Id())
    .List<Guid>();

Few point of interest here:

{alias} will lead to the root entity alias (employee) in my case. Unfortunately, you cannot use any other aliases;
for sql query I have used value - it will replace ?, but as it is a string - I had to escape it with "'".

CreateCriteria over a collection of simple types

2 Answers2