For queries where the criteria are not known in advance, such as a website's advanced product search, ICriteria queries are more appropriate than named HQL queries. In this recipe, we will show you how to use the same DAL infrastructure with ICriteria and QueryOver queries.
- In
Eg.Core.Data.Impl.Queries, add a new, empty, public interface namedICriteriaQuery. - Add a class named
CriteriaQueryBasewith the following code:public abstract class CriteriaQueryBase<TResult> : NHibernateQueryBase<TResult>, ICriteriaQuery { public CriteriaQueryBase(ISessionFactory sessionFactory) : base(sessionFactory) { } public override TResult Execute() { var criteria = GetCriteria(); return WithinTransaction(() => Execute(criteria)); } protected abstract ICriteria GetCriteria(); protected abstract TResult Execute(ICriteria criteria); } - In
Eg.Core.Data.Queries, add the following enum:public enum AdvancedProductSearchOrderDirection { Ascending, Descending } - Add a new interface named
IAdvancedProductSearchwith the following code:public interface IAdvancedProductSearch : IQuery<IEnumerable<Product>> { string Name { get; set; } string Description { get; set; } decimal? MinimumPrice { get; set; } decimal? MaximumPrice { get; set; } string OrderBy { get; set; } AdvancedProductSearchOrderDirection OrderDirection { get; set; } } - In
Eg.Core.Data.Impl.Queries, add the following class:public class AdvancedProductSearch : CriteriaQueryBase<IEnumerable<Product>>, IAdvancedProductSearch { public AdvancedProductSearch( ISessionFactory sessionFactory) : base(sessionFactory) { } public string Name { get; set; } public string Description { get; set; } public decimal? MinimumPrice { get; set; } public decimal? MaximumPrice { get; set; } public string OrderBy { get; set; } public AdvancedProductSearchOrderDirection OrderDirection { get; set; } protected override ICriteria GetCriteria() { return GetProductQuery().UnderlyingCriteria; } protected override IEnumerable<Product> Execute( ICriteria criteria) { return criteria.List<Product>(); } private IQueryOver GetProductQuery() { var query = session.QueryOver<Product>(); AddProductCriterion(query); return query; } private void AddProductCriterion( IQueryOver<Product, Product> query) { if (!string.IsNullOrEmpty(Name)) query = query.WhereRestrictionOn(p => p.Name) .IsInsensitiveLike(Name, MatchMode.Anywhere); if (!string.IsNullOrEmpty(Description)) query.WhereRestrictionOn(p => p.Description) .IsInsensitiveLike(Description, MatchMode.Anywhere); if (MinimumPrice.HasValue) query.Where(p => p.UnitPrice >= MinimumPrice); if (MaximumPrice.HasValue) query.Where(p => p.UnitPrice <= MaximumPrice); if (!string.IsNullOrEmpty(OrderBy)) { var order = Property.ForName(OrderBy)); switch (OrderDirection) { case AdvanceProductSearchOrderDirection.Descending: query = query.OrderBy(order).Desc; break; case AdvanceProductSearchOrderDirection.Ascending: query = query.OrderBy(order).Asc; break; } } else { query = query.OrderBy(p => p.UnitPrice).Asc; } } }
In this recipe, we reuse the repository and query infrastructure from the Using named queries in the data access layer recipe. Our simple base class for ICriteria-based query objects splits query creation from query execution and handles transactions for us automatically.
The example query we use is typical of an advanced product search use case. When a user fills in a particular field on the UI, the corresponding criterion is included in the query. When the user leaves the field blank, we ignore it.
We check each search parameter for data. If the parameter has data, we add the appropriate criterion to the query. Finally, we set the order by clause, based on the OrderBy parameter, and return the completed ICriteria query. The query is executed inside a transaction and the results are returned.
For this type of query, each query parameter would be set to the value of some field on your product search UI. On using this query, your code looks as shown:
var query = repository.CreateQuery<IAdvancedProductSearch>(); query.Name = searchData.PartialName; query.Description = searchData.PartialDescription; query.MinimumPrice = searchData.MinimumPrice; query.MaximumPrice = searchData.MaximumPrice; query.OrderBy = searchData.OrderBy; query.OrderDirection = searchData.OrderDirection; var results = query.Execute();
You may have noticed that we used the same CreateQuery method in this recipe as we did in the previous named query recipe. This means that we have successfully hidden the querying technique used from the consuming code. If there's suddenly a need to switch from named queries to criteria queries, HQL, LINQ, or even plain SQL, it can be done without affecting the rest of the application code. All that's needed is a new class implementing the correct IQuery interface.