NHibernate Tutorial 6: Querying in NHibernate

What Will I Learn?

You will learn querying with NHibernate.

Requirements

• NHibernate
• Fluent NHibernate
• Entity Framework

Difficulty

Basic

Tutorial Contents

• Entity Framework's Query API
• Querying in NHibernate

Curriculum

• Preparing The System To Use NHibernate In An Application
• Implementing and mapping the remaining domain objects
• Using the Auto Mapping Feature
• Second-level Caching with Entity Framework & NHibernate
• Self-tracking entities

NHibernate Tutorial 6: Querying in NHibernate

Providing an engine to query the domain model is a primary responsibility of any DAL. If your DAL is then based on an O/RM tool, then providing an API to query the model become s a primary responsibility of the O/RM of choice. For this reason, some O/RM products offer their own variation of the SQL language adapted to work on the domain model. So you have Entity SQL with Entity Framework and the HQL language in NHibernate. More in general each O/RM provides its own specific API for you to arrange a query using criteria, projections, sort expressions as run time conditions and specific use-cases suggest. The query API is just an implementation of the Query Object pattern.

In this field, the advent of LINQ with the .NET Framework has in a way clouded the issue. On one end, LINQ offers a programmer-friendly syntax and shields from syntax aspects of the query implementation; on the other end, LINQ is just a top-level syntax working over some underlying machinery whose real implementation plays its own role in determining performance and functionality. Fact is that LINQ raised the bar of developers' expectations as far as model queries are concerned. Cutting edge O/RMs today must have a LINQ provider.

While having a LINQ provider is generally a great plus, there are other aspects to consider in the evaluation of the query capabilities of an O/RM tool. Fetching data is made of ad hoc criteria-based queries but also of quick and frequently repeated reads that would originate an individual round trip with subsequent extra load on the database and the network. The ability of batching queries into a single round trip to the database gives you more programming power and represents a tool to effectively fight the notorious SELECT N+1 problem.

Let's explore what Entity Framework and NHibernate has to offer when it comes to their query capabilities.

Entity Framework's Query API

Entity Framework offers two distinct API to build and execute your queries - the Entity SQL language and the popular LINQ-to-Entities framework.

The Entity SQL language offers a SQL-like syntax to query the abstract database represented by the entity model. It looks a lot like plain SQL except that instead of table names you use collection of entities and instead of column names you use property names. Here's a quick example: 

const String command = "SELECT VALUE customer FROM NorthwindEntities.Customers 



                               AS customer 



                               WHERE customer.CustomerID='ALFKI'";



using (var context = new NorthwindEntities())



{



     var query = new ObjectQuery<Customer>(command, context);



     var result = query.Execute(MergeOption.NoTracking);



     var alfki = result.FirstOrDefault();



     if (alfki != null)



         Console.WriteLine(alfki.CompanyName);



}

It should be noted that in Entity SQL all column references must be qualified with the table alias. In other words, the following classic query command won't be accepted:

SELECT CompanyName FROM Customers

It must be rewritten as follows:

SELECT VALUE t.CompanyName FROM NorthwindEntities.Customers AS t

The use of the VALUE keyword serves the purpose of avoiding the automatic generation of a row wrapper. It indicates that all you want are plain values. As mentioned, Entity SQL looks like T-SQL (and other SQL dialects for other DBMS systems other than SQL Server) but it is not the same. A list of the differences between T-SQL and Entity SQL is summarized here. In particular, you'll find out that Entity SQL does not support batching query results. Two concatenated and individually valid Entity SQL commands will report a syntax error when you try to execute.

Entity SQL is just an attempt of porting the familiar SQL language to the world of entities and domain model. By using the old-fashioned syntax of SQL, you express criteria for the query. Other O/RMs such as NHibernate offered a brand new and object-based approach to defining queries. In light of the release of a LINQ provider in NHibernate, I dare say that while nobody can question the power and flexibility of a criteria-based API, the ease of a LINQ provider still wins.

So in Entity Framework, LINQ-to-Entities works side by side with the Entity SQL language and allows to do the same things just using an easier syntax that is integrated in the language and doesn't rely on opaque strings. If you mistype a property name in a LINQ query, or if you end up with a wrong piece of syntax, the IntelliSense first and the compiler later will get back to you quite soon with suggestions. You don't have to wait to run the command to find out what may have gone wrong.

With LINQ- to-Entities you have the full power of the LINQ syntax with no limitations as far JOINs and GROUPs are concerned. When using LINQ-to-Entities, however, it is fundamental to compile queries programmatically to gain a bit of performance. Because a LINQ-to-Entities query represents a thick abstraction over the actual SQL Server query machinery some work is required to transform it. This is what is usually referred to as "compiling the query". Notice that this step adds on any other compile work the DBMS may do internally. If you reusing the same query over and over again, you can save some time by precompiling it. Here's how you can do it.

You first declare a static variable that will hold the compiled query and make it available to any part of your code that may need it.

static Func<NorthwindEntities, IQueryable<String>> _compiledQueryFunc;

Next, you precompile the query as early as possible in your application. 

static readonly object 



_locker = new object();



static void PrecompileLinqQueries()



{



    lock (_locker)



    {



        if (_compiledQueryFunc == null)



        {



            _compiledQueryFunc = CompiledQuery.Compile<NorthwindEntities, 



                                                       IQueryable<String>>



                (context => from c in context.Customers select c.CompanyName);



        }



    }



}

Because Entity Framework is not thread-safe, you might want to use a thread lock. When you use the lock keyword on a .NET object, then only one thread at a time is allowed to grab the synchronizing object. If other threads are concurrently trying to access it, they are blocked until the lock is released and served on a first-in-first-out basis later. Consider that you really need a lock here if your code is subject to concurrent access - for example, a Web application. Otherwise, you can do without it.

Invoking a compiled query is easy too. All you do is calling the method Invoke on the global Func object that represents the query. 

using (var context = new NorthwindEntities())



{



    var customers = _compiledQueryFunc.Invoke(context).ToList();



    foreach (var s in customers)



        Console.WriteLine(s);



}

It goes without saying that when you execute a precompiled query you must provide a valid Entity Framework context object. You don't need any when you just compile the query. The Invoke method returns an IQueryable object that you can build your expressions on.Precompiled LINQ queries are faster than queries compiled on the fly just because on repeated access you save the extra time of compiling. In any case, compiled LINQ queries are on the average slower than native ADO.NET queries. You never choose an O/RM for raw performance.

Querying in NHibernate

In NHibernate you have more query options than with Entity Framework which, as usual is good and bad news. It is great as you can likely find what you really need and can craft your perfect solution. The bad news is that if you don't have specific needs, and all you want is fetching a bunch of table rows as objects, then multiple options can only add noise and confuse. Chances to pick up an API stabbing in the dark are high.

To start off, NHibernate offers the HQL language which is similar to SQL and represents the preferred choice of people coming to O/RM tools with a strong SQL background. Next, you have the criteria API. Here's an example that picks up all customers whose name starts with A and returns the list of at most 20 company names ordered ascending. 

var customers = session.CreateCriteria(typeof(Customer))



                    .Add(Expression.Like("CompanyName", "A%"))



                    .AddOrder(Order.Asc("CompanyName"))



                    .SetProjection(Projections.Property("CompanyName"))



                    .SetMaxResults(20)



                    .List();

If you dig out the documentation you'll find out that the possibilities of building just the query you like are high. The main benefit of the criteria API is that you can programmatically construct the query and can do that just when you need and using all the run time information that are required.

The support for LINQ queries has been recently integrated in NHibernate, but the code has been tested in production for about a couple of years. This is to say that it's not firsthand code. The LINQ-to-NHibernate provider is entirely based on the existing criteria API and provides the beautiful and strong typed syntax you may know: 

var customers = (from c in session.Query<Customer>()



                 where c.CompanyName.StartsWith("A")



                 orderby c.CompanyName ascending



                 select c).ToList();                

A significant change happened to the LINQ provider in NHibernate. It is no longer limited to exposing the criteria API as it was the case for a version available before NHibernate. It has now been rewritten to target HQL rather than the criteria API and is nearly equivalent to the Entity Framework provider. Compiled queries work in the same way.

NHibernate also adds the QueryOver API for queries. QueryOver is essentially a wrapper around the criteria API that uses .NET extension methods and lambda expressions to let you express criteria in a type safe way. The previous query used to illustrate the criteria API can be rewritten as follows: 

var customers = 



session.QueryOver<Customer>() 



                    .WhereRestrictionOn(c => c.CompanyName).IsLike("A%")



                    .OrderBy(c => c.CompanyName).Asc



                    .Select(c => c. CompanyName)



                    .Take(20)



                    .List();

Without beating around the bush, the QueryOver API looks a lot like a LINQ provider. As far as NHibernate is concerned, the difference is clear. QueryOver is not a LINQ provider and is limited to supporting the criteria API. This means that the native LINQ provider in NHibernate which targets HQL is more powerful as it can address advanced scenarios such as those involving group joins. The reason for having yet another query API like QueryOver is simply to remove magic strings and add static type safety.

Finally, compared to Entity Framework NHibernate offers a few other possibilities and control. For example, in NHibernate you have multi-queries - that is, the ability to batch multiple queries in a single round trip to the database. This feature is going to work if the database supports returning multiple result sets. Microsoft SQL Server and SQLite support this. You use the CreateMultiQuery method if you intend to run HQL queries; you use the CreateMultiCriteria method for criteria-based queries. 

var results = session.CreateMultiQuery()    



                     .Add(session.CreateQuery("...")        



                     .Add("select ...")    



                     .List();



var rs1 = results[0];



var rs2 = results[1];

Futures are another smart form of query batching that NHibernate allows. Basically, it all consists of calling the method Future instead of List in a query. 

var customers = session.CreateCriteria(typeof(Customer))



                       .Future();



var orders = session.CreateCriteria(typeof(Order))



                    .Future();

The method List would execute any query immediately resulting in two distinct roundtrips; the method Future just waits for real use of the data of any queries collected along the way. With Future, a query is added to a dynamically created multi-criteria query which executes in a single round trip upon first access. Interestingly enough, the results you get are definitive and you won't be connecting again to the database as you enumerate through any of the collections.

Comparing Entity Framework and NHibernate on the subject of data query is fairly easy and emblematic of the whole comparison. NHibernate has usually more options and more control. Entity Framework is kind of simpler but equally effective to take projects home. More options, however, are not a benefit in all cases. If you don't know how to choose and don't fully understand the power of the tool, you can make mistakes. While with NHibernate you can achieve unparalleled heights of programming power, with Entity Framework you can hardly do the wrong thing. Both do pretty well the important things; on details they differ a bit. As this is the final article on the series comparing NHibernate and Entity Framework, I sum it up by saying that the biggest differences between the two (but not necessarily enough to justify a choice) are the lack of a second level cache and multi-queries in Entity Framework. 

---

Posted on Utopian.io - Rewarding Open Source Contributors

---