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.
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.
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.
