Filter on joins in nHibernate

Question

I’m trying a build an nHibernate criteria expression to perform a search.

Given the following data model:

• An operation may have zero or more sessions.
• An operation may be of zero of more operation types.

I want to search for all sessions based on the following criteria:

• (Mandatory) Where the operation IsActive flag is true, IsPublished flag is true

o (Optionally) and where the operation state/end dates are within the user specified date range

o (Optionally) and where the session regionid matches a user specified id

o (Optionally) and where the session division matches a user specified id

o (Optionally) and where the Operation.OperationTypes are in a user specified list of type ids

I would express this in sql as (given all optional parameters have been supplied):

SELECT     
    [Session].*
FROM         
    [OperationTypeOperation] 
LEFT OUTER JOIN
    [Operation] ON [OperationTypeOperation].[OperationId] = [Operation].[OperationId]
RIGHT OUTER JOIN
    [Session] ON [Operation].[OperationId] = [Session].[OperationId]
WHERE
    ([Operation].[IsPublished] = 1) 
AND 
    ([Operation].[IsActive] = 1) 
AND 
    ([Session].[RegionId] = 66)
AND 
    ([Session].[DivisionId] = 99)
AND 
    ([Operation].[AdvertisingStartDate] < GETDATE()) 
AND 
    ([Operation].[AdvertisingEndDate] > GETDATE()) 
AND 
    ([OperationTypeOperation].[OperationTypeId] IN (1, 2, 3))

And in my nHibernate query as:

public PagedResult<Session> Search(int? regionId, int? divisionId, DateTime? startDate, DateTime? endDate, IList<int> operationTypeId, int itemsPerPage, int page)
     {

      var criteria = _session.CreateCriteria(typeof(Session))
          .Add(Expression.Eq("IsActive", true))
          .Add(Expression.Eq("AcceptingApplications", true))
          .AddOrder(new Order("StartDate", false))
          ;

      if (regionId.HasValue)
          criteria.Add(Expression.Eq("Region.Id", regionId.Value));

      if (divisionId.HasValue)
          criteria.Add(Expression.Eq("Division.Id", divisionId.Value));

      if (startDate.HasValue)
          criteria.Add(Expression.Ge("StartDate", startDate.Value));

      if (endDate.HasValue)
          criteria.Add(Expression.Le("EndDate", endDate.Value));

      //Add the operation types
      if (operationTypeId.Count > 0)
      {
          var operationTypes = new Collection<OperationType>();
       foreach (int id in operationTypeId)
          {
              operationTypes.Add(_session.Get<OperationType>(id));
          }
       //Join on the operations
       criteria.CreateCriteria("Operation")
       .Add(Expression.Eq("IsPublished", true))
       .Add(Expression.Eq("IsActive", true))
       .Add(Expression.Le("AdvertisingStartDate", DateTime.Now))
       .Add(Expression.Ge("AdvertisingEndDate", DateTime.Now))
       .CreateAlias("OperationTypes", "operationTypes", JoinType.InnerJoin)
       .Add(Expression.In("OperationTypes", operationTypes))
       .SetResultTransformer(new DistinctRootEntityResultTransformer())
       ;
      }
      else
      {
       //Join on the operations
       criteria.CreateCriteria("Operation")
       .Add(Expression.Eq("IsPublished", true))
       .Add(Expression.Eq("IsActive", true))
       .Add(Expression.Le("AdvertisingStartDate", DateTime.Now))
       .Add(Expression.Ge("AdvertisingEndDate", DateTime.Now))
       ;
      }

      return criteria.ToPagedResult<Session>(itemsPerPage, page);
     }

My nHibernate function is complaining about the operation types and throwing an exception “Cannot use collections with InExpression”. Plus I’m not sure if I’m filtering on the joined tables correctly. Can anyone weigh in the correct way to write the above sql as an nHibernate expression?

Answer 1

I assume that OperationType is an entity class (not an enum). You cannot use In with a list of entities. You can join using the id.

criteria
  .CreateCriteria("Operation")
  // add other expressions
  .CreateCriteria("OperationTypes", "operationTypes", JoinType.LeftOuterJoin)
  .Add(Expression.In("operationTypes.Id", operationTypeId))

I assume that _session.Get(id) performs a database query. You should avoid this anyway.

PS: If OperationType is mapped as a composite collection (with the <composite-element> tag) than, unfortunately, you cannot join it with criteria. I'm not sure if there is a workaround. But there is a patch pending.

Answer 2

I think there is a better way than writing :

criteria
  .CreateCriteria("Operation")
  // add other expressions
  .CreateCriteria("OperationTypes", "operationTypes", JoinType.LeftOuterJoin)
  .Add(Expression.In("operationTypes.Id", operationTypeId))

because additional join is performed on "Operation"

You can simply write:

criteria.Add(Expression.In("OperationTypes", operationTypeId))
.CreateCriteria("Operation")
                        .Add(Expression.Eq("IsPublished", true))
                        .Add(Expression.Eq("IsActive", true))
                        .Add(Expression.Le("AdvertisingStartDate", DateTime.Now))
                        .Add(Expression.Ge("AdvertisingEndDate", DateTime.Now))
                        .SetResultTransformer(new DistinctRootEntityResultTransformer())