这个是QueryOver的第二部分，主要介绍投影，分页，子查询的实例应用。
投影且把投影结果转成DTO （Projection）
订单DTO类：OrderDTO
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace MyWorkShop.Model.DTOs
{
public class OrderDTO
{
public Guid Id { get; set; }
public string CustomerName { get; set; }
public DateTime OrderedDateTime { get; set; }
public Decimal? Amount { get; set; }
}
}
根据订单号查找订单，并用LINQ TO Object转成OrderDTO
public OrderDTO GetOrderDTOById(Guid id)
{
OrderDTO dto = null;
Customer customer = null;
using (var session = NHibernateSession)
using (var transaction = session.BeginTransaction())
{
dto = session.QueryOver<Order>()
.JoinAlias(o => o.Customer, () => customer)
.Where(o => o.Id == id)
.Select(o => o.Id, o => customer.Name, o => o.OrderedDateTime,o => o.Amount)
.List<object[]>()
.Select(props => new OrderDTO
{
Id = (Guid)props[0],
CustomerName=(string)props[1],
OrderedDateTime = (DateTime)props[2],
Amount = (decimal)props[3]
}).SingleOrDefault();
transaction.Commit();
}
return dto;
}
等效SQL：
SELECT
this_.Id as y0_,
customer1_.Name as y1_,
this_.OrderedDateTime as y2_,
this_.Amount as y3_
FROM MyWorkShop_Order this_ inner
join MyWorkShop_Customer customer1_
on this_.CustomerId=customer1_.Id
WHERE this_.Id = @p0;
@p0 = b0a7f211-0404-4df5-93be-9ee501216c5c
代码说明：
1. 由于OrderDTO包含CustomerName字段，而该字段的值取自Customer实体类，所以需对Order与Customer进行内连接，关于内连接的操作请见《NHibernate中的QueryOver查询(No.1)》；
2. 代码中的第一个Select进行投影(Projection)操作，取出所要的4个字段，分别为o.Id、customer.Name、o.OrderedDateTime、o.Amount；
3. .List<object[]>()把投影得到的4个字段放到一个object[]数组中；
4. 代码中的第二个Select使用LINQ TO Object（此时与NHibernate无关），新建一个OrderDTO对象，并把object[]数组的4个字段依次赋给OrderDTO对象，字段赋值之前需进行强制类型转换，把object类型转成相应的类型；
5. 由于需对每个字段进行强制类型转换，所以代码不太干净且容易出错，而且当字段类型变化时需手工修改代码，不利于代码重构，所以不推荐使用此方案，较好的方案是下面介绍的使用NHibernate内置方法把投影结果转成DTO。
根据订单号查找订单，并用NHibernate内置方法转成OrderDTO
public OrderDTO GetOrderDTOById(Guid id)
{
OrderDTO dto = null;
//定义用于内连接的别名变量，该变量必须赋值为null
Customer customer = null;
using (var session = NHibernateSession)
using (var transaction = session.BeginTransaction())
{
dto = session.QueryOver<Order>()
//创建用于内连接的别名customer
.JoinAlias(o => o.Customer, () => customer)
.Where(o => o.Id == id)
.SelectList(list =>list
.Select(o => o.Id).WithAlias(() => dto.Id) //给投影列取别名，用于把投影结果转成DTO
.Select(o => customer.Name).WithAlias(() => dto.CustomerName)
.Select(o => o.OrderedDateTime).WithAlias(() => dto.OrderedDateTime)
.Select(o => o.Amount).WithAlias(() => dto.Amount)
)
//把投影结果转成DTO
.TransformUsing(Transformers.AliasToBean<OrderDTO>())
.SingleOrDefault<OrderDTO>();
transaction.Commit();
}
return dto;
}
等效SQL：
SELECT this_.Id as y0_,
customer1_.Name as y1_,
this_.OrderedDateTime as y2_,
this_.Amount as y3_
FROM MyWorkShop_Order this_ inner
join MyWorkShop_Customer customer1_
on this_.CustomerId=customer1_.Id
WHERE this_.Id = @p0;
@p0 = b0a7f211-0404-4df5-93be-9ee501216c5c
代码说明：
1. SelectList()包含要投影的列；
2. WithAlias()给每个投影得到的列取别名，用于投影结果转DTO；
3. .TransformUsing(Transformers.AliasToBean<OrderDTO>())把投影结果转DTO。
分组统计（Group）
1. 统计每个客户所有订单的总金额,以及客户Id
public IEnumerable<CustomerIdAndTotalAmountDTO> GetCustomerIdAndTotalAmountDTOs()
{
CustomerIdAndTotalAmountDTO dto = null;
IEnumerable<CustomerIdAndTotalAmountDTO> retList = null;
using (var session = NHibernateSession)
using (var transaction = session.BeginTransaction())
{
retList = session.QueryOver<Order>()
.SelectList(list => list
.SelectGroup(o => o.Customer.Id).WithAlias(() => dto.CustomerId)
.SelectSum(o => o.Amount).WithAlias(() => dto.TotalAmount)
)
.TransformUsing(Transformers.AliasToBean<CustomerIdAndTotalAmountDTO>())
.List<CustomerIdAndTotalAmountDTO>();
transaction.Commit();
}
return retList;
}
等效SQL：
NHibernate:
SELECT
this_.CustomerId as y0_,
sum(this_.Amount) as y1_
FROM MyWorkShop_Order this_
GROUP BY this_.CustomerId
代码说明：
1. .SelectGroup(o => o.Customer.Id)指定分组的列；
2. .SelectSum(o => o.Amount)指定对Amount调用求和聚集函数，除了SelectSum外还有SelectAvg求平均、SelectCount计数、SelectMax求最大、SelectMin求最小等常见的聚集函数。
分页（Paging） 分页查找
public IEnumerable<OrderDTO> GetOrderDTOsByPage(int pageIndex, int pageSize)
{
OrderDTO dto = null;
Customer customer = null;
IEnumerable<OrderDTO> retList = null;
using (var session = NHibernateSession)
using (var transaction = session.BeginTransaction())
{
retList = session.QueryOver<Order>()
.JoinAlias(o => o.Customer, () => customer)
.SelectList(list => list
.Select(o => o.Id).WithAlias(() => dto.Id)
.Select(o => customer.Name).WithAlias(() => dto.CustomerName)
.Select(o => o.OrderedDateTime).WithAlias(() => dto.OrderedDateTime)
.Select(o => o.Amount).WithAlias(() => dto.Amount)
)
.TransformUsing(Transformers.AliasToBean<OrderDTO>())
.OrderBy(o=>o.Amount).Desc
.Skip(pageIndex * pageSize)
.Take(pageSize)
.List<OrderDTO>();
transaction.Commit();
}
return retList;
}
等效SQL：
SELECT
TOP (@p0) y0_,
y1_,
y2_,
y3_ FROM (SELECT this_.Id as y0_,
customer1_.Name as y1_,
this_.OrderedDateTime as y2_,
this_.Amount as y3_,
ROW_NUMBER() OVER(ORDER BY this_.Amount DESC) as __hibernate_sort_row
FROM MyWorkShop_Order this_
inner join MyWorkShop_Customer customer1_
on this_.CustomerId=customer1_.Id) as query
WHERE query.__hibernate_sort_row > @p1
ORDER BY query.__hibernate_sort_row;
@p0 = 2 [Type: Int32 (0)],
@p1 = 2 [Type: Int32 (0)]
代码说明：
1. 调用Skip()、Take()实现数据分页读取。
2.计算数据总量
public int GetOrderCount()
{
using (var session = NHibernateSession)
using (var transaction = session.BeginTransaction())
{
int count = session.QueryOver<Order>()
.RowCount();
transaction.Commit();
return count;
}
}
等效SQL：
SELECT count(*) as y0_ FROM MyWorkShop_Order this_
代码说明：
1. 调用RowCount()计算数据总量。
子查询（Subquery）
1. 查找金额最大的订单
public Order GetMaxAmountOrder()
{
Order order = null;
using (var session = NHibernateSession)
using (var transaction = session.BeginTransaction())
{
var maxAmount = NHibernate.Criterion.QueryOver.Of<Order>()
.SelectList(a=>a.SelectMax(o=>o.Amount));
order = session.QueryOver<Order>()
.WithSubquery
.WhereProperty(o => o.Amount)
.Eq(maxAmount)
.SingleOrDefault();
transaction.Commit();
}
return order;
}
等效SQL：
SELECT this_.Id as Id9_0_,
this_.CustomerId as CustomerId9_0_,
this_.OrderedDateTime as OrderedD3_9_0_,
this_.Amount as Amount9_0_
FROM MyWorkShop_Order this_
WHERE this_.Amount = (SELECT max(this_0_.Amount) as y0_ FROM MyWorkShop_Order this_0_)
代码说明：
1. .WithSubquery指定子查询。