NHibernate - QueryOver Queries

In this chapter, we will be covering QueryOver Queries. It is a new syntax which is more like LINQ using the method chain syntax as shown in the following query.

var customers = session.QueryOver<Customer>() .Where(x => x.FirstName == "Laverne");

  • It is still criteria under the covers, but now our queries are strongly typed.

  • As we have seen in the criteria query, the first name is just an opaque string, now we're actually using an x.FirstName, so the first name gets refactored and renamed that gets changed in the link style criteria query using the query over.

  • We can still do many similar things, but you cannot use the query comprehension syntax with query over, you have to use the method chain syntax and you can't mix and match the link and the criteria.

  • For a lot of queries, the query over API is very useful and provides a much easier to comprehend object syntax than using Criteria directly.

Let’s have a look into a simple example in which we will retrieve a customer whose first name is Laverne using a query over.

using System; 
using System.Data; 
using System.Linq; 
using System.Reflection; 

using HibernatingRhinos.Profiler.Appender.NHibernate; 
using NHibernate.Cfg; 
using NHibernate.Criterion; 
using NHibernate.Dialect; 
using NHibernate.Driver; 
using NHibernate.Linq;

namespace NHibernateDemo { 

   internal class Program { 
      
      private static void Main() { 
		
         var cfg = ConfigureNHibernate(); 
         var sessionFactory = cfg.BuildSessionFactory();
         using(var session = sessionFactory.OpenSession()) 
         
         using(var tx = session.BeginTransaction()) { 
            var customers = session.QueryOver<Customer>() 
               .Where(x => x.FirstName == "Laverne"); 
            
            foreach (var customer in customers.List()) { 
               Console.WriteLine(customer); 
            } 
				
            tx.Commit(); 
         }
			
         Console.WriteLine("Press <ENTER> to exit..."); 
         Console.ReadLine(); 
      }
      
      private static Configuration ConfigureNHibernate() { 
		
         NHibernateProfiler.Initialize();
         var cfg = new Configuration(); 
         
         cfg.DataBaseIntegration(x => { 
            x.ConnectionStringName = "default"; 
            x.Driver<SqlClientDriver>(); 
            x.Dialect<MsSql2008Dialect>(); 
            x.IsolationLevel = IsolationLevel.RepeatableRead; 
            x.Timeout = 10; 
            x.BatchSize = 10; 
         });
			
         cfg.SessionFactory().GenerateStatistics();
         cfg.AddAssembly(Assembly.GetExecutingAssembly()); 
         return cfg; 
      } 
   } 
}

As you can see that it is still Criteria underneath the covers, but is just a nicer syntax.

When the above code is compiled and executed, you will see the following output.

Laverne Hegmann (4e97c816-6bce-11e1-b095-6cf049ee52be)
   Points: 74
   HasGoldStatus: True
   MemberSince: 4/4/2009 12:00:00 AM (Utc)
   CreditRating: Neutral
   AverageRating: 0

   Orders:
      Order Id: 4ea14d96-6bce-11e1-b095-6cf049ee52be
      Order Id: 4ea14d96-6bce-11e1-b096-6cf049ee52be
      Order Id: 4ea14d96-6bce-11e1-b097-6cf049ee52be
      Order Id: 4ea14d96-6bce-11e1-b098-6cf049ee52be
		
Press <ENTER> to exit...

One of the disadvantages is that, let's say we want to say that FirstName.StartsWith(“A”) as shown in the following program.

var customers = session.QueryOver<Customer>() .Where(x => x.FirstName.StartsWith("A"));
 
foreach (var customer in customers.List()) { 
   Console.WriteLine(customer); 
} 

tx.Commit();

Now let’s run the application again and you will see that this is not a LINQ provider as it doesn't know what this StartsWith method is, so you will get a RunTime exception.

Run Time Exception

The exception says unrecognized method call. Here we are doing the obvious thing, but it doesn't necessarily work.

Let's try something else, like FirstName is equal to “A%” as shown in the following code.

var customers = session.QueryOver<Customer>() .Where(x => x.FirstName == "A%"); 

foreach (var customer in customers.List()) { 
   Console.WriteLine(customer); 
}

Let’s run this once again and you will see that we're not going to get any results back as shown below.

Press <ENTER> to exit...

To understand this why we are not getting any results, let’s have a look at NHibernate profiler.

Results NHibernate Profile

As you can see that the first name is equal to A% which is not. A% is used in SQL using with the like operator. Now we need to create a restriction into WHERE clause as shown in the following program.

var customers = session.QueryOver<Customer>() 
   .Where(Restrictions.On<Customer>(c => c.FirstName).IsLike("A%")); 
	
foreach (var customer in customers.List()) { 
   Console.WriteLine(customer); 
}

Let’s run your application again and you will see that all the customers are retrieved with first name starts with A.

Alejandrin Will (4ea3aef6-6bce-11e1-b0b4-6cf049ee52be)
   Points: 24
   HasGoldStatus: False
   MemberSince: 10/1/2011 12:00:00 AM (Utc)
   CreditRating: VeryVeryGood
   AverageRating: 0

   Orders:
      Order Id: 4ea3aef6-6bce-11e1-b0b5-6cf049ee52be

Austyn Nolan (4ea871b6-6bce-11e1-b110-6cf049ee52be)
   Points: 67
   HasGoldStatus: True
   MemberSince: 12/29/2007 12:00:00 AM (Utc)
   CreditRating: Neutral
   AverageRating: 0

   Orders:
      Order Id: 4ea871b6-6bce-11e1-b111-6cf049ee52be

Antonia Murphy (4ea871b6-6bce-11e1-b121-6cf049ee52be)
   Points: 72
   HasGoldStatus: True
   MemberSince: 6/15/2009 12:00:00 AM (Utc)
   CreditRating: Terrible
   AverageRating: 0

   Orders:
      Order Id: 4ea871b6-6bce-11e1-b122-6cf049ee52be
      Order Id: 4ea871b6-6bce-11e1-b123-6cf049ee52be
      Order Id: 4ea871b6-6bce-11e1-b124-6cf049ee52be
      Order Id: 4ea871b6-6bce-11e1-b125-6cf049ee52be
      Order Id: 4ea871b6-6bce-11e1-b126-6cf049ee52be
      Order Id: 4ea871b6-6bce-11e1-b127-6cf049ee52be
      Order Id: 4ea871b6-6bce-11e1-b128-6cf049ee52be
      Order Id: 4ea871b6-6bce-11e1-b129-6cf049ee52be
      Order Id: 4ea871b6-6bce-11e1-b12a-6cf049ee52be

It works the same way as it did before, except using this new QueryOver syntax. Many developers find that LINQ syntax is more approachable and often does the right things.

If LINQ can't handle it, then you will start looking at HQL or Criteria to see if that's going to be more suitable.

It just gives you a different syntax, so Criteria, both the create criteria and the QueryOver provide you just yet another querying mechanism that allows you to pull data out of the database using NHibernate.

Advertisements