NHibernate Querying in Core 2.2 Framework

Introduction

NHibernate O/RM framework is one of the competitor's Entity Framework and they are the most popular in the old framework of .NET.

NHibernate architecture applied the separation of concerns, for example, it ensures a separation between the Unit Of Work and its configuration (Entities and Mapping).

It was always complicated for some developers to work on the mapping class in NHibernate because you have to do it manually but it was easier in Entity Framework, even both of them, they support the POCO (Plain old CLR object) in entities creation. And the most important thing for an ORM is the way of searching data from the database, the time consumed if we try to fetch some information according to a specific query with more than criteria that can be requested from more than a table should be minimized, if we have a huge data in our database, optimizing response time becomes more and more complicated, some developers add a stored procedure to be called after from ORM as an optimization, NHibernate has several querying APIs, we will describe in this article Criteria API and QueryOver that is similar.

Background

You need to have an experience with Fluent NHibernate to understand the difference.

Querying

Criteria API

As an ORM, NHibernate manipulates objects not tables, and the use of this feature is simple, intuitive and extensible.

To use Criteria query API, we need start by creating a new instance of NHibernate.ICriteria, it is associated with a persistent entity and represents query against it.

This is an example for a Person Entity:

ICriteria criteria = currentSession.CreateCriteria<Person>();

Let’s manipulate this criterion to get what we need as results. If we want to get the list of all Persons:

currentSession.CreateCriteria(typeof(T).List<T>() or we call our instance in this way: 
criteria.List<Person>();

If we need to search a Person by the Id for example:

Person person = currentSession.Get<Person>(id) or criteria.Get<Person>(id)

These results can be filtered by applying restrictions to our Criteria Query. So, we use a Restrictions factory class that define a number of methods available on ICriteria interface and passing them to an Add method.

Let’s take a look at this example:

Criteria.Add(Restrictions.Eq("Name", "Rayen"))

Or we can instantiate a new object having a type IQueryCriterion restrictions.

var restrictions = new Conjunction(); restrictions.Add
(new LikeCriterion<A>(c => c.Name, "Rayen", true, MatchingMode.Anywhere));

Or if we want to be equals, we can call the second method:

restrictions.Add(new EqualCriterion<A>(c => c.Name, "Rayen"));

And this is the method LikeCriterion:

private LikeCriterion(Expression<Func<A, object>> propertyExpression, 
string value, bool insensitive, MatchingMode matchMode)
{
PropertyExpression = propertyExpression;
Value = value;
MatchMode = matchMode;
Insensitive = insensitive;
}
public EqualCriterion(Expression<Func<A, object>> propertyExpression, object value)
{
PropertyExpression = propertyExpression;
Value = value;
}
var persons = currentSession.CreateCriteria(typeof(Person)).Add(restrictions);

And in this example, we introduce the notion of Expression that can be grouped.

We can order our results after filtering using NHibernate.Expression.Order in this way.

var personOrdered = criteria.AddOrder( Order.Asc("Name") ).SetMaxResults(100).List<Person>();

We can work using Projections that use the factory class NHibernate.Expression.Projections IProjection instances.

It’s applied by calling the method SetProjection().

The factory methods allow the projected value to be referred to by criterion and order instances.

This is an example that gets the count.

public int GetCount(IQueryCriterion restrictions)
{
var criteria = _currentSession.CreateCriteria(typeof(T))
.SetProjection(Projections.RowCount
return (int)criteria.UniqueResult();
}

Query Over API

Criteria API is powerful in building queries dynamically that improve the search in applications. But as we see in our samples, properties are hardcoded in criteria queries.

For this reason, we will use QueryOver to enhance criteria queries.

Let’s rewrite previous samples but by integrating QueryOver.

criteria.List<Person>();
criteria.QueryOver<Person>().List();
var persons = currentSession.CreateCriteria(typeof(Person)).Add(restrictions);
var persons = currentSession.QueryOver<Person>().Where(restrictions).List();

QueryOver is using Where and it’s similar to a SQL Query, it includes many features, for example, TransformUsing that Transform the results using the supplied IResultTransformer.

IResultTransformer is an Implementors that define a strategy for transforming criteria query results into the actual application-visible query result list.

We can use OrderBy in this way:

We have a method in the IRepository:

IEnumerable<T> GetAllOrderBy(Expression<Func<T, bool>> restrictions, 
Expression<Func<T, object>> order, bool ascending = false);

And the implementation will be in Repository Class.

public IEnumerable<T> GetAllOrderBy(Expression<Func<T, bool>> restrictions, 
Expression<Func<T, object>> orderByProperty, bool ascending = false)
{
var query = currentSession.QueryOver<T>().Where(restrictions);
return ascending
? query.OrderBy(orderByProperty).Asc.List()
: query.OrderBy(orderByProperty).Desc.List();
}

Where T is Person.

And this is the call in the Business Layer where we fill all parameters:

var Persons = _personRepository.GetAllOrderBy(e => e.Name == "Rayen", e=>e.CreationTime, true);

For more details, check out this link.