NHibernate Querying In Core 2.2 Framework

Introduction

NHibernate architecture applies 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; however, it was easier in Entity Framework even when both of them 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. If we try to fetch some information according to a specific query with more than the criteria to be requested from more than a table, the time consumed should be minimized. If we have 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 discuss Criteria API and QueryOver, which are similar to each other, in this article.

Querying - Criteria API

As an ORM, NHibernate manipulates the objects, not tables; and the use of this feature is simple, intuitive, and extensible. To use Criteria Query API, we need to start by creating a new instance of NHibernate.ICriteria. It is associated with a persistent entity and represents the query against it.

This is an example of 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 -

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 defines 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 equal, 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 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 Implementor that defines 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);

Linq Queries

Linq queries are created from ISession, let’s use the previous sample:

var Persons =currentSession.Query<Person>() .Where(c => c.Name == "Rayen").ToList();

Future results are supported by Linq and used in Core 2.1 framework in NHibernate, they are a deferred query result. It can be used in this way:

IFutureEnumerable< Person > Persons =session.Query<Person>().Where(c => c.Name == "Rayen").ToFuture();

Native SQL

NHibernate allows the developers to use a native SQL including stored procedures for all create, update, select and delete operations.

The following example shows how to get entity objects from a native SQL query via AddEntity().

currentSession.CreateSQLQuery("SELECT * FROM PERSON").AddEntity(typeof(Person));

Conclusion

In this article, we described a flip side of multiple ways of querying methods using NHibernate, we covered some querying syntax but refer to NHibernate Documentation to find more features but which one we can use for a beginner? It depends on the complexity of your Queries; every method has a limitation.