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).
- The fluent style works well; it's nice to use
- It's codegen-able like a crazy thing
- It's flexible and very extensible
- It performs - the generated SQL is lean and exactly what you need, it's measurably faster than L2S or Lightspeed
- It isn't Linq-to-SQL
- 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: -
- Use a transaction
- Use a projection
I'll write some drivel about projections at a later date.
Toodles.
