”;
In this chapter, we will be covering another common API that people will use is the NHibernate LINQ provider. Its access through an extension method on ISession and the signature is a Query <T>. There are two types of syntax while using LINQ −
- Query Chaining Syntax
- Query Comprehension Syntax
Query Chaining Syntax
You can access any record from the database using the method chain syntax as shown in the following program.
var customer = session.Query<Customer>() .Where(c => c.FirstName == "Laverne")
-
You can see that we have query, and also WHERE clause, you can have additional WHERE clauses and similarly select clause.
-
This is a standard method chain syntax that you can use in normal LINQ.
-
LINQ to Objects or LINQ to SQL, any of the other LINQ providers you might be familiar with.
Let’s have a look into a simple example in which we will retrieve the customer whose first name is Laverne. Now it is a possibility that we might have more than one customer whose first name is Laverne, so we will retrieve the first one only.
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 customer = session.Query<Customer>() .Where(c => c.FirstName == "Laverne").First(); 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; } } }
Now 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...
Query Comprehension Syntax
There”s also the query comprehensions syntax, which looks a lot more like SQL using the from, where and select keywords.
So let”s have a look into the same example, but this time we use LINQ comprehensions syntax, which looks a lot more like SQL as shown in the following program.
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 customer = (from c in session.Query<Customer>() where c.FirstName == "Laverne" select c).First(); 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; } } }
Now let’s run this application again and 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...
Let’s have a look into another example in which we will retrieve all those customers, whose FirstName starts with the letter H.
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.Query<Customer>() .Where(c =< c.FirstName.StartsWith("H")); foreach (var customer in customers.ToList()) { 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; } } }
Similarly, the query comprehension syntax will look like the following program.
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 = from c in session.Query<Customer>() where c.FirstName.StartsWith("H") select c; foreach (var customer in customers.ToList()) { 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; } } }
Let’s run this application again and you will see all the customers, whose first name starts with the alphabet H.
Herman Crooks (4ead3480-6bce-11e1-b15c-6cf049ee52be) Points: 74 HasGoldStatus: True MemberSince: 12/3/2010 12:00:00 AM (Utc) CreditRating: Neutral AverageRating: 0 Orders: Order Id: 4ead3480-6bce-11e1-b15d-6cf049ee52be Order Id: 4ead3480-6bce-11e1-b15e-6cf049ee52be Order Id: 4ead3480-6bce-11e1-b15f-6cf049ee52be Order Id: 4ead3480-6bce-11e1-b160-6cf049ee52be Order Id: 4ead3480-6bce-11e1-b161-6cf049ee52be Order Id: 4ead3480-6bce-11e1-b162-6cf049ee52be Order Id: 4ead3480-6bce-11e1-b163-6cf049ee52be Hudson Bins (4ec03f80-6bce-11e1-b2b7-6cf049ee52be) Points: 56 HasGoldStatus: False MemberSince: 10/20/2008 12:00:00 AM (Utc) CreditRating: Terrible AverageRating: 0 Orders: Order Id: 4ec03f80-6bce-11e1-b2b8-6cf049ee52be Order Id: 4ec03f80-6bce-11e1-b2b9-6cf049ee52be Order Id: 4ec03f80-6bce-11e1-b2ba-6cf049ee52be Order Id: 4ec03f80-6bce-11e1-b2bb-6cf049ee52be Order Id: 4ec03f80-6bce-11e1-b2bc-6cf049ee52be Order Id: 4ec03f80-6bce-11e1-b2bd-6cf049ee52be Order Id: 4ec03f80-6bce-11e1-b2be-6cf049ee52be Order Id: 4ec03f80-6bce-11e1-b2bf-6cf049ee52be Hettie Feest (4ec50240-6bce-11e1-b300-6cf049ee52be) Points: 82 HasGoldStatus: False MemberSince: 4/10/2009 12:00:00 AM (Utc) CreditRating: Neutral AverageRating: 0 Orders: Order Id: 4ec50240-6bce-11e1-b301-6cf049ee52be Order Id: 4ec50240-6bce-11e1-b302-6cf049ee52be Order Id: 4ec50240-6bce-11e1-b303-6cf049ee52be Press <ENTER> to exit...
”;