在NHibernate 3.0的时候开始，NHibernate引入了新的查询API —— QueryOver。这中查询是构建在NHibernate原有的ICriteria API之上的，支持Lambda表达式和扩展方法。
实例场景：
客户(Customer)与订单(Order)，一个客户可以下多个订单。
实体基类： Entity
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace MyWorkShop.Model.Entities
{
public abstract class Entity<TId>
{
public virtual TId Id { get; protected set; }
}
}
客户类：Customer，映射的数据表为MyWorkShop_Customer
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace MyWorkShop.Model.Entities
{
public class Customer : Entity<int>
{
public virtual string Name { get; set; }
public virtual string Address { get; set; }
public virtual string Phone { get; set; }
}
}
订单类：Order，映射的数据表为MyWorkShop_Order
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace MyWorkShop.Model.Entities
{
//Guid:主键
public class Order : Entity<Guid>
{
//下单客户
public virtual Customer Customer { get; set; }
//下单时间
public virtual DateTime OrderedDateTime { get; set; }
//金额
public virtual Decimal? Amount { get; set; }
}
}
查询场景
筛选数据（Restriction）
根据客户姓名查找客户，假设客户姓名唯一
public Customer GetByName(string customerName)
{
Customer entity = null;
using (var session = NHibernateSession)
using (var transaction = session.BeginTransaction())
{
entity = session.QueryOver<Customer>()
.Where(c => c.Name == customerName)
.SingleOrDefault();
transaction.Commit();
}
return entity;
}
等价SQL:
SELECT this_.Id as Id5_0_, this_.Name as Name5_0_, this_.Address as Address5_0_, this_.Phone as Phone5_0_ FROM MyWorkShop_Customer this_ WHERE this_.Name = @p0;@p0 = 'Name' [Type: String (50)]
代码说明：
1. 筛选条件调用Where方法，使用Lambda表达式“c => c.Name == customerName”，这样就消除了ICriteria的字段名字符串硬编码的问题；
2. 返回单个值调用SingleOrDefault()，若查询结果不唯一则抛出异常NHibernate.NonUniqueResultException。
根据客户地址查找多个客户
public IEnumerable<Customer> GetByAddress(string address)
{
IEnumerable<Customer> list = null;
using (var session = NHibernateSession)
using (var transaction = session.BeginTransaction())
{
list = session.QueryOver<Customer>()
.Where(c => c.Address == address)
.List();
transaction.Commit();
}
return list;
}
等效SQL：
SELECT this_.Id as Id5_0_, this_.Name as Name5_0_, this_.Address as Address5_0_, this_.Phone as Phone5_0_ FROM MyWorkShop_Customer this_ WHERE this_.Address = @p0;@p0 = 'Address' [Type: String (100)]
代码说明：
1. 查询多条数据调用List()方法。
根据客户姓名模糊查找客户
public IEnumerable<Customer> GetByLikeName(string likeName)
{
IEnumerable<Customer> list = null;
using (var session = NHibernateSession)
using (var transaction = session.BeginTransaction())
{
list = session.QueryOver<Customer>()
.WhereRestrictionOn(o => o.Name)
.IsLike(likeName, MatchMode.Anywhere)
.List();
transaction.Commit();
}
return list;
}
等效SQL
SELECT this_.Id as Id5_0_, this_.Name as Name5_0_, this_.Address as Address5_0_, this_.Phone as Phone5_0_ FROM MyWorkShop_Customer this_ WHERE this_.Name like @p0;@p0 = '%e%' [Type: String (50)]
代码说明：
1. 对于某些SQL函数与操作符（比如like、between...and...），没有直接对应的Lambda表达式，需要先使用WhereRestrictionOn方法指定筛选条件的列，然后再调用相应的方法指定筛选条件；
2. IsLike方法指定字符串匹配查找。
查找金额在指定范围内的订单
public IEnumerable<Order> GetByAmount(decimal minAmount, decimal maxAmount)
{
IEnumerable<Order> list = null;
using (var session = NHibernateSession)
using (var transaction = session.BeginTransaction())
{
list = session.QueryOver<Order>()
.Where(o => o.Amount >= minAmount)
.And(o => o.Amount <= maxAmount)
.OrderBy(o => o.Amount)
.Desc
.List();
transaction.Commit();
}
return list;
}
等效SQL：
SELECT this_.Id as Id8_0_, this_.CustomerId as CustomerId8_0_, this_.OrderedDateTime as OrderedD3_8_0_, this_.Amount as Amount8_0_ FROM MyWorkShop_Order this_ WHERE this_.Amount >= @p0 and this_.Amount <= @p1 ORDER BY this_.Amount desc;@p0 = 100 [Type: Decimal (0)], @p1 = 200 [Type: Decimal (0)]
代码说明：
1. 多个条件可使用Where...And...逐个指定，也可以在一个Where方法中指定，比如上面的条件可以写成Where(o => o.Amount >= minAmount && o.Amount <= maxAmount)；
2. 排序使用OrderBy，升序降序使用Asc与Desc。
连接（Join）
内连接：根据客户姓名查找订单
public IEnumerable<Order> GetByCustomerName(string customerName)
{
IEnumerable<Order> list = null;
using (var session = NHibernateSession)
using (var transaction = session.BeginTransaction())
{
list = session.QueryOver<Order>()
.OrderBy(o=>o.Amount).Desc
.Inner
.JoinQueryOver<Customer>(o => o.Customer)
.Where(c => c.Name == customerName)
.List();
transaction.Commit();
}
return list;
}
等效SQL：
SELECT this_.Id as Id8_1_, this_.CustomerId as CustomerId8_1_, this_.OrderedDateTime as OrderedD3_8_1_, this_.Amount as Amount8_1_, customer1_.Id as Id9_0_, customer1_.Name as Name9_0_, customer1_.Address as Address9_0_, customer1_.Phone as Phone9_0_ FROM MyWorkShop_Order this_ inner join MyWorkShop_Customer customer1_ on this_.CustomerId=customer1_.Id WHERE customer1_.Name = @p0 ORDER BY this_.Amount desc;@p0 = 'Name' [Type: String (50)
代码说明：
1. Inner.JoinQueryOver指定内连接，如果省略Inner仅写JoinQueryOver默认就是内连接；
2. Left、.Right则分别为左外连接、右外连接
使用别名进行内连接：根据客户姓名查找订单
public IEnumerable<Order> GetByCustomerNameViaAlias(string customerName)
{
//定义用于内连接的别名变量，该变量必须赋值为null
Customer customer = null;
IEnumerable<Order> list = null;
using (var session = NHibernateSession)
using (var transaction = session.BeginTransaction())
{
list = session.QueryOver<Order>()
.JoinAlias(o => o.Customer, () => customer) //指定别名customer
.Where(() => customer.Name == customerName)
.List();
transaction.Commit();
}
return list;
}
等效SQL：
SELECT this_.Id as Id8_1_, this_.CustomerId as CustomerId8_1_, this_.OrderedDateTime as OrderedD3_8_1_, this_.Amount as Amount8_1_, customer1_.Id as Id9_0_, customer1_.Name as Name9_0_, customer1_.Address as Address9_0_, customer1_.Phone as Phone9_0_ FROM MyWorkShop_Order this_ inner join MyWorkShop_Customer customer1_ on this_.CustomerId=customer1_.Id WHERE customer1_.Name = @p0 ORDER BY this_.Amount desc;@p0 = 'Name' [Type: String (50)]
代码说明：
1. 可以通过.Inner.JoinQueryOver来显式进行内连接，也可以通过.JoinAlias创建连接别名进行连接；
2. 连接别名变量在QueryOver使用之前定义，并且必须赋null值。
与ICriteria API相比，个人认为QueryOver并不见得会提高代码的可读性，但QueryOver解决了
ICriteria API字符串硬编码的问题，从而减少代码输入的错误，大大提高了代码重构的能力，因此用QueryOver取代ICriteria API是值得的。