NHibernate 3.0: Using ICriteria and Paged Queries in the Data Access Layer

Exclusive offer: get 50% off this eBook here
NHibernate 3.0 Cookbook

NHibernate 3.0 Cookbook — Save 50%

Get solutions to common NHibernate problems to develop high-quality performance-critical data access applications

$26.99    $13.50
by Jason Dentler | October 2010 | .NET Cookbooks Open Source Web Development

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. This article by Jason Dentler, author of NHibernate 3.0 Cookbook, shows how to use the same DAL infrastructure with ICriteria and QueryOver queries.

In an effort to avoid overwhelming the user, and increase application responsiveness, large result sets are commonly broken into smaller pages of results. This article also shows how we can easily add paging to a QueryOver query object in our DAL.

 

NHibernate 3.0 Cookbook

NHibernate 3.0 Cookbook

Get solutions to common NHibernate problems to develop high-quality performance-critical data access applications

  • Master the full range of NHibernate features
  • Reduce hours of application development time and get better application architecture and performance
  • Create, maintain, and update your database structure automatically with the help of NHibernate
  • Written and tested for NHibernate 3.0 with input from the development team distilled in to easily accessible concepts and examples
  • Part of Packt's Cookbook series: each recipe is a carefully organized sequence of instructions to complete the task as efficiently as possible
        Read more about this book      

(For more resources on NHibernate, see here.)

Using ICriteria in the data access layer

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. This article by Jason Dentler, author of NHibernate 3.0 Cookbook, shows how to use the same DAL infrastructure with ICriteria and QueryOver queries.

In an effort to avoid overwhelming the user, and increase application responsiveness, large result sets are commonly broken into smaller pages of results. This article also shows how we can easily add paging to a QueryOver query object in our DAL.

Getting ready

Complete the previous recipe, Using Named Queries in the data access layer.

How to do it...

  1. In Eg.Core.Data.Impl.Queries, add a new, empty, public interface named ICriteriaQuery.
  2. Add a class named CriteriaQueryBase with 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 Transact(() => Execute(criteria));
    }

    protected abstract ICriteria GetCriteria();

    protected abstract TResult Execute(ICriteria criteria);
    }

  3. In Eg.Core.Data.Queries, add the following enum:

    public enum AdvancedProductSearchSort
    {
    PriceAsc,
    PriceDesc,
    Name
    }

  4. Add a new interface named IAdvancedProductSearch with 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; }
    AdvancedProductSearchSort Sort { get; set; }
    }

  5. 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 AdvancedProductSearchSort
    Sort { 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<T>(
    IQueryOver<T, T> query) where T : Product
    {
    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);

    switch (Sort)
    {
    case AdvancedProductSearchSort.PriceDesc:
    query = query.OrderBy(p => p.UnitPrice).Desc;
    break;
    case AdvancedProductSearchSort.Name:
    query = query.OrderBy(p => p.Name).Asc;
    break;
    default:
    query = query.OrderBy(p => p.UnitPrice).Asc;
    break;
    }
    }
    }

How it works...

In this recipe, we reuse the same 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 for 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 Sort parameter and return the completed ICriteria query. The query is executed inside a transaction, and the results are returned.

There's more...

For this type of query, typically, each query parameter would be set to the value of some field on your product search UI. On using this query, your code looks like this:

var query = repository.CreateQuery<IAdvancedProductSearch>();
query.Name = searchCriteria.PartialName;
query.Description = searchCriteria.PartialDescription;
query.MinimumPrice = searchCriteria.MinimumPrice;
query.MaximumPrice = searchCriteria.MaximumPrice;
query.Sort = searchCriteria.Sort;
var results = query.Execute();

NHibernate 3.0 Cookbook Get solutions to common NHibernate problems to develop high-quality performance-critical data access applications
Published: October 2010
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:

Using Paged Queries in the data access layer

Complete the recipe, Using Named Queries in the data access layer.

How to do it...

  1. In Eg.Core.Data.Queries, add a class using the following code:

    public class PagedResult<T>
    {
    public int TotalItems { get; set; }
    public IEnumerable<T> PageOfResults { get; set; }
    }

  2. Add an interface using the following code:


    public interface IPagedQuery<T>
    : IQuery<PagedResult<T>>
    {
    int PageNumber { get; set; }
    int ItemsPerPage { get; set; }
    }

  3. In Eg.Core.Data.Impl.Queries, add the following class:

    public abstract class PagedQueryOverBase<T>
    : NHibernateQueryBase<PagedResult<T>>,
    IPagedQuery<T>
    {

    public PagedQueryOverBase(ISessionFactory sessionFactory)
    : base(sessionFactory) { }

    public int PageNumber { get; set; }

    public int ItemsPerPage { get; set; }

    public override PagedResult<T> Execute()
    {
    var query = GetQuery();
    SetPaging(query);
    return Transact(() => Execute(query));
    }

    protected abstract IQueryOver<T, T> GetQuery();

    protected virtual void SetPaging(
    IQueryOver<T, T> query)
    {
    int maxResults = ItemsPerPage;
    int firstResult = (PageNumber - 1) * ItemsPerPage;
    query.Skip(firstResult).Take(maxResults);
    }

    protected virtual PagedResult<T> Execute(
    IQueryOver<T, T> query)
    {
    var results = query.Future<T>();
    var count = query.ToRowCountQuery().FutureValue<int>();
    return new PagedResult<T>()
    {
    PageOfResults = results,
    TotalItems = count.Value
    };
    }
    }

  4. In Eg.Core.Data.Queries, add an interface for the example query:

    public interface IPagedProductSearch
    : IPagedQuery<Product>
    {
    string Name { get; set; }
    string Description { get; set; }
    decimal? MinimumPrice { get; set; }
    decimal? MaximumPrice { get; set; }
    PagedProductSearchSort Sort { get; set; }
    }

  5. Add the following enumeration for choosing the sort option:

    public enum PagedProductSearchSort
    {
    PriceAsc,
    PriceDesc,
    Name
    }

  6. In Eg.Core.Data.Impl.Queries, implement the interface using the following class:

    public class PagedProductSearch
    : PagedQueryOverBase<Product>,
    IPagedProductSearch
    {

    public PagedProductSearch(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 PagedProductSearchSort
    Sort { get; set; }

    protected override IQueryOver<Product, Product> GetQuery()
    {
    var query = session.QueryOver<Product>();
    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);
    switch (Sort)
    {
    case PagedProductSearchSort.PriceDesc:
    query = query.OrderBy(p => p.UnitPrice).Desc;
    break;
    case PagedProductSearchSort.Name:
    query = query.OrderBy(p => p.Name).Asc;
    break;
    default:
    query = query.OrderBy(p => p.UnitPrice).Asc;
    break;
    }
    return query;
    }
    }

How it works...

In this recipe, we've defined a common PagedResult<T> return type for all paged queries. We've also defined the IPagedQuery<T> interface, which specifies the paging parameters and a return type of PagedResult<T>.

As defined in PagedQueryOverBase, each subclassed query object must return a standard IQueryOver<T, T> query from GetQuery(). The PagedQueryOverBase class sets the appropriate Skip and Take values based on the specified page number and items per page. Then it uses futures to get the results. The row count query is created from the result set query using the new ToRowCountQuery() method. The future queries are executed when the count query result is put into the PagedResult<T> object.

Summary

In this article we covered:

  • Using ICriteria in the data access layer
  • Using Paged Queries in the data access layer

In the next article we will use LINQ specifications in the data access layer


Further resources on this subject:


NHibernate 3.0 Cookbook Get solutions to common NHibernate problems to develop high-quality performance-critical data access applications
Published: October 2010
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:

About the Author :


Jason Dentler

Jason Dentler grew up in the small Texas town of Mission Valley. He started tinkering with computers as a kid in the late 1980s, and all these years later, he hasn't stopped. He's worked in a few different industries. Currently, he builds really awesome software in higher education. He's an Eagle Scout and a graduate of the University of Houston – Victoria.

Books From Packt


NHibernate 2 Beginner's Guide
NHibernate 2 Beginner's Guide

WCF 4.0 Multi-tier Services Development with LINQ to Entities
WCF 4.0 Multi-tier Services Development with LINQ to Entities

ASP.NET 3.5 Application Architecture and Design
ASP.NET 3.5 Application Architecture and Design

Microsoft Windows Communication Foundation 4.0 Cookbook for Developing SOA Applications
Microsoft Windows Communication Foundation 4.0 Cookbook for Developing SOA Applications

.NET Compact Framework 3.5 Data Driven Applications
.NET Compact Framework 3.5 Data Driven Applications

Microsoft Azure: Enterprise Application Development
Microsoft Azure: Enterprise Application Development

Applied Architecture Patterns on the Microsoft Platform
Applied Architecture Patterns on the Microsoft Platform

Microsoft Windows Workflow Foundation 4.0 Cookbook
Microsoft Windows Workflow Foundation 4.0 Cookbook


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
7
X
p
X
d
R
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software