nHibernate 3.0 QueryOver - Searching, and Paging at database level

I've been through numerous data-access technologies with .NET - plain old DataReaders & SqlCommands, Code-genned layers around these of various descriptions and varying degrees of horrible, Pile of Code, Typed Datasets, Linq-to-SQL, Lightspeed, LLBLGen Pro, Entity Failwork.

Of all these, nHibernate 3.0 is definitely one of them. No doubt something better is on the horizon and when it looms into view I might take a look. Until then, this is working well for me.

In my humble opinion, NH3 is way better than Linq-to-Fail or infact any other model-based ORF (Object-Relational Failfactory).

1. The fluent style works well; it's nice to use
2. It's codegen-able like a crazy thing
3. It's flexible and very extensible
4. It performs - the generated SQL is lean and exactly what you need, it's measurably faster than L2S or Lightspeed
5. It isn't Linq-to-SQL
6. or a cabbage

NH3 gives us a whole set of functionality missing from 2.1 - functions that wrap typical SQL functions. It also gives us a lot of additional flexibility with lambda's prevalent over magic strings, so refactoring and catching problems at design time is now a doddle.

That said, I'd like a nicer way of doing multi-field searches; maybe by using an Expression<ICriteria> as a parameter to my search method ?

Here is my search method, abbreviated so as not to cause boredom: -

public IList CustomerSearch(CustomerSearchCriteria criteria, int pageIndex, int pageSize)
        {
            IList list = null;

            using (var session = SessionFactory.OpenSession())
            {
                var query = session.QueryOver();

                if (!string.IsNullOrEmpty(criteria.CustomerName))
                {
                    MatchMode matchMode;
                     
                    if (criteria.CustomerSearch == CustomerSearchEnum.StartingWith)
                    {
                        matchMode = MatchMode.Start;
                    }
                    else if ..
                    // etc

                    query = query.WhereRestrictionOn(x => x.CustomerName.ToLower()).IsLike(criteria.CustomerName.ToLower(), matchMode);
                }
                
                if (criteria.ActiveOnly)
                {
                    query = query.And(x => x.Active == true);
                }
                if (!string.IsNullOrEmpty(criteria.Address1))
                {
                    query = query.And(x => x.Address1.ToLower().StartsWith(criteria.Address1.ToLower()));
                }
                // etc

I'm working on a nicer way of doing this, so stay tuned. That said, the above is fairly readable; I'm just appending additional sections to the WHERE clause where needed.

Hmm.. Anyway, one thing NH3 gives us is database-level paging using the SQL Server ROW_NUMBER function, which is efficient (much more efficient than selecting the whole set into memory and doing the paging there..).

list = query.Skip(pageIndex * pageSize)
    .Take(pageSize)
    .OrderBy(x => x.CustomerName).Asc()
    .List();

This is nice, except it *only* returns the rows in the page. How do I know how many rows there are in total, so I can set up my pager accordingly ?

This is easy when done at stored procedure level, as I can just run an additional Count(*) SELECT and return an OUT parameter, all in one hit to the database. Am I going to need two hits here ?

As it turns out, the CriteriaTransformer has a "TransformToRowCount" method, which does what we need. This does issue one more SQL statement for the count, and it's submitted separately - so two round trips, not ideal.

list = query.Skip(pageIndex * pageSize)
    .Take(pageSize)
    .OrderBy(x => x.CustomerName).Asc()
    .List();

int count = CriteriaTransformer.TransformToRowCount(query.UnderlyingCriteria)
                        .UniqueResult();

And after reading this, the process has been simplified even further with the addition of an extension method. So the previous example can be re-written as just ..

list = query.Skip(pageIndex * pageSize)
    .Take(pageSize)
    .OrderBy(x => x.CustomerName).Asc()
    .List();

int count = query.RowCount();

Nice ! But still two database hits. There are two ways to fix this: -

1. Use a transaction
2. Use a projection

I'll write some drivel about projections at a later date.

Toodles.