Geek Answers Handbook

Filter by connections in nHibernate

I am trying to build an nHibernate criteria expression to do a search.

Given the following data model:

  • An operation may have zero or more sessions.
  • An operation can be equal to zero more types of operations.

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

  • (Required) If the IsActive Flag operation is true, the IsPublished flag is true

o (Optional), and where the dates / statuses of the operation are within the user-specified date range

o (optional) and where the session area matches the user id specified

o (optional) and where session splitting matches user-specified id

o (optional) and where Operation.OperationTypes types are in the user-specified list of type identifiers

I would say this in sql as (if all the additional parameters were provided):

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 request, like:

 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 complains about operation types and throws an exception "Cannot use collections with InExpression". Plus Im not sure if Im filters on joined tables correctly. Can someone properly weigh the record of the above sql as an expression nHibernate?

+4
source share
2 answers

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

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

I assume that _session.Get (id) is executing a database query. You should still avoid this.

PS: If an OperationType is displayed as a composite collection (with the <composite-element> ), unfortunately, you cannot join it with the criteria. I am not sure there is a workaround. But there is a fix.

+5
source

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))

since the additional connection 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())
+1
source

More articles:


All Articles