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 it’s 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 become 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.
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 to 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 enhances 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
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);
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.
