一、引言
NHibernate3.0增加了一種新的查詢(xún)API——QueryOver。QueryOver構(gòu)建在NHibernate原有的 ICriteria API之上，支持Lambda表達(dá)式與擴(kuò)展方法，可編寫(xiě)類(lèi)型安全的查詢(xún)語(yǔ)句，這樣就克服了ICriteria API字符串硬編碼的弊端。在上一篇文章中《NHibernate 3.x新功能實(shí)踐（一） QueryOver（上）》通過(guò)一個(gè)簡(jiǎn)單的實(shí)例，介紹了QueryOver進(jìn)行條件篩選（Restriction）、連接（Join）等應(yīng)用，在這篇文章中將介紹投影(Projection)、把投影結(jié)果轉(zhuǎn)成DTO、分頁(yè)、子查詢(xún)（Subquery）等常見(jiàn)應(yīng)用場(chǎng)景。 在文章《NHibernate 3.x新功能實(shí)踐（一） QueryOver（上）》最后提供實(shí)例源代碼下載。
二、開(kāi)發(fā)環(huán)境與工具
參見(jiàn)《NHibernate 3.x新功能實(shí)踐（一） QueryOver（上）》
三、實(shí)例場(chǎng)景
參見(jiàn)《NHibernate 3.x新功能實(shí)踐（一） QueryOver（上）》四、查詢(xún)場(chǎng)景
1. 投影且把投影結(jié)果轉(zhuǎn)成DTO （Projection）
訂單DTO類(lèi)：OrderDTO
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5
6 namespace MyWorkShop.Model.DTOs
7 {
8 public class OrderDTO
9 {
10 public Guid Id { get; set; }
11 public string CustomerName { get; set; }
12 public DateTime OrderedDateTime { get; set; }
13 public Decimal? Amount { get; set; }
14 }
15 }
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5
6 namespace MyWorkShop.Model.DTOs
7 {
8 public class OrderDTO
9 {
10 public Guid Id { get; set; }
11 public string CustomerName { get; set; }
12 public DateTime OrderedDateTime { get; set; }
13 public Decimal? Amount { get; set; }
14 }
15 }
（1）根據(jù)訂單號(hào)查找訂單，并用LINQ TO Object轉(zhuǎn)成OrderDTO
1 public OrderDTO GetOrderDTOById(Guid id)
2 {
3 OrderDTO dto = null;
4
5 Customer customer = null;
6
7 using (var session = NHibernateSession)
8 using (var transaction = session.BeginTransaction())
9 {
10 dto = session.QueryOver<Order>()
11 .JoinAlias(o => o.Customer, () => customer)
12 .Where(o => o.Id == id)
13 .Select(o => o.Id, o => customer.Name, o => o.OrderedDateTime,o => o.Amount)
14 .List<object[]>()
15 .Select(props => new OrderDTO
16 {
17 Id = (Guid)props[0],
18 CustomerName=(string)props[1],
19 OrderedDateTime = (DateTime)props[2],
20 Amount = (decimal)props[3]
21 }).SingleOrDefault();
22
23 transaction.Commit();
24 }
25
26 return dto;
27 }
2 {
3 OrderDTO dto = null;
4
5 Customer customer = null;
6
7 using (var session = NHibernateSession)
8 using (var transaction = session.BeginTransaction())
9 {
10 dto = session.QueryOver<Order>()
11 .JoinAlias(o => o.Customer, () => customer)
12 .Where(o => o.Id == id)
13 .Select(o => o.Id, o => customer.Name, o => o.OrderedDateTime,o => o.Amount)
14 .List<object[]>()
15 .Select(props => new OrderDTO
16 {
17 Id = (Guid)props[0],
18 CustomerName=(string)props[1],
19 OrderedDateTime = (DateTime)props[2],
20 Amount = (decimal)props[3]
21 }).SingleOrDefault();
22
23 transaction.Commit();
24 }
25
26 return dto;
27 }
輸出的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
代碼說(shuō)明：
- 由于OrderDTO包含CustomerName字段，而該字段的值取自Customer實(shí)體類(lèi)，所以需對(duì)Order與Customer進(jìn)行內(nèi)連接，關(guān)于內(nèi)連接的操作請(qǐng)參見(jiàn)上一篇文章《NHibernate 3.x新功能實(shí)踐（一） QueryOver（上）》；
- 代碼中的第一個(gè)Select進(jìn)行投影(Projection)操作，取出所要的4個(gè)字段，分別為o.Id、customer.Name、o.OrderedDateTime、o.Amount；
- .List<object[]>()把投影得到的4個(gè)字段放到一個(gè)object[]數(shù)組中；
- 代碼中的第二個(gè)Select使用LINQ TO Object（此時(shí)與NHibernate無(wú)關(guān)），新建一個(gè)OrderDTO對(duì)象，并把object[]數(shù)組的4個(gè)字段依次賦給OrderDTO對(duì)象，字段賦值之前需進(jìn)行強(qiáng)制類(lèi)型轉(zhuǎn)換，把object類(lèi)型轉(zhuǎn)成相應(yīng)的類(lèi)型；
- 由于需對(duì)每個(gè)字段進(jìn)行強(qiáng)制類(lèi)型轉(zhuǎn)換，所以代碼不太干凈且容易出錯(cuò)，而且當(dāng)字段類(lèi)型變化時(shí)需手工修改代碼，不利于代碼重構(gòu)，所以不推薦使用此方案，較好的方案是下面介紹的使用NHibernate內(nèi)置方法把投影結(jié)果轉(zhuǎn)成DTO。
1 public OrderDTO GetOrderDTOById(Guid id)
2 {
3 OrderDTO dto = null;
4
5 //定義用于內(nèi)連接的別名變量，該變量必須賦值為null
6 Customer customer = null;
7
8 using (var session = NHibernateSession)
9 using (var transaction = session.BeginTransaction())
10 {
11 dto = session.QueryOver<Order>()
12 //創(chuàng)建用于內(nèi)連接的別名customer
13 .JoinAlias(o => o.Customer, () => customer)
14 .Where(o => o.Id == id)
15 .SelectList(list =>list
16 .Select(o => o.Id).WithAlias(() => dto.Id) //給投影列取別名，用于把投影結(jié)果轉(zhuǎn)成DTO
17 .Select(o => customer.Name).WithAlias(() => dto.CustomerName)
18 .Select(o => o.OrderedDateTime).WithAlias(() => dto.OrderedDateTime)
19 .Select(o => o.Amount).WithAlias(() => dto.Amount)
20 )
21 //把投影結(jié)果轉(zhuǎn)成DTO
22 .TransformUsing(Transformers.AliasToBean<OrderDTO>())
23 .SingleOrDefault<OrderDTO>();
24
25 transaction.Commit();
26 }
27
28 return dto;
29 }
2 {
3 OrderDTO dto = null;
4
5 //定義用于內(nèi)連接的別名變量，該變量必須賦值為null
6 Customer customer = null;
7
8 using (var session = NHibernateSession)
9 using (var transaction = session.BeginTransaction())
10 {
11 dto = session.QueryOver<Order>()
12 //創(chuàng)建用于內(nèi)連接的別名customer
13 .JoinAlias(o => o.Customer, () => customer)
14 .Where(o => o.Id == id)
15 .SelectList(list =>list
16 .Select(o => o.Id).WithAlias(() => dto.Id) //給投影列取別名，用于把投影結(jié)果轉(zhuǎn)成DTO
17 .Select(o => customer.Name).WithAlias(() => dto.CustomerName)
18 .Select(o => o.OrderedDateTime).WithAlias(() => dto.OrderedDateTime)
19 .Select(o => o.Amount).WithAlias(() => dto.Amount)
20 )
21 //把投影結(jié)果轉(zhuǎn)成DTO
22 .TransformUsing(Transformers.AliasToBean<OrderDTO>())
23 .SingleOrDefault<OrderDTO>();
24
25 transaction.Commit();
26 }
27
28 return dto;
29 }
輸出的SQL：
同上，略
代碼說(shuō)明：
- SelectList()包含要投影的列；
- WithAlias()給每個(gè)投影得到的列取別名，用于投影結(jié)果轉(zhuǎn)DTO；
- .TransformUsing(Transformers.AliasToBean<OrderDTO>())把投影結(jié)果轉(zhuǎn)DTO。
2. 分組統(tǒng)計(jì)（Group）
（1）統(tǒng)計(jì)每個(gè)客戶(hù)所有訂單的總金額,以及客戶(hù)Id
1 public IEnumerable<CustomerIdAndTotalAmountDTO> GetCustomerIdAndTotalAmountDTOs()
2 {
3 CustomerIdAndTotalAmountDTO dto = null;
4
5 IEnumerable<CustomerIdAndTotalAmountDTO> retList = null;
6
7 using (var session = NHibernateSession)
8 using (var transaction = session.BeginTransaction())
9 {
10 retList = session.QueryOver<Order>()
11 .SelectList(list => list
12 .SelectGroup(o => o.Customer.Id).WithAlias(() => dto.CustomerId)
13 .SelectSum(o => o.Amount).WithAlias(() => dto.TotalAmount)
14 )
15 .TransformUsing(Transformers.AliasToBean<CustomerIdAndTotalAmountDTO>())
16 .List<CustomerIdAndTotalAmountDTO>();
17
18 transaction.Commit();
19 }
20
21 return retList;
22 }
2 {
3 CustomerIdAndTotalAmountDTO dto = null;
4
5 IEnumerable<CustomerIdAndTotalAmountDTO> retList = null;
6
7 using (var session = NHibernateSession)
8 using (var transaction = session.BeginTransaction())
9 {
10 retList = session.QueryOver<Order>()
11 .SelectList(list => list
12 .SelectGroup(o => o.Customer.Id).WithAlias(() => dto.CustomerId)
13 .SelectSum(o => o.Amount).WithAlias(() => dto.TotalAmount)
14 )
15 .TransformUsing(Transformers.AliasToBean<CustomerIdAndTotalAmountDTO>())
16 .List<CustomerIdAndTotalAmountDTO>();
17
18 transaction.Commit();
19 }
20
21 return retList;
22 }
輸出的SQL：
NHibernate: SELECT this_.CustomerId as y0_, sum(this_.Amount) as y1_ FROM MyWorkShop_Order this_ GROUP BY this_.CustomerId
代碼說(shuō)明：
- .SelectGroup(o => o.Customer.Id)指定分組的列；
- .SelectSum(o => o.Amount)指定對(duì)Amount調(diào)用求和聚集函數(shù)，除了SelectSum外還有SelectAvg求平均、SelectCount計(jì)數(shù)、SelectMax求最大、SelectMin求最小等常見(jiàn)的聚集函數(shù)。
3. 分頁(yè)（Paging）
（1）分頁(yè)查找
1 public IEnumerable<OrderDTO> GetOrderDTOsByPage(int pageIndex, int pageSize)
2 {
3 OrderDTO dto = null;
4 Customer customer = null;
5
6 IEnumerable<OrderDTO> retList = null;
7
8 using (var session = NHibernateSession)
9 using (var transaction = session.BeginTransaction())
10 {
11
12 retList = session.QueryOver<Order>()
13 .JoinAlias(o => o.Customer, () => customer)
14 .SelectList(list => list
15 .Select(o => o.Id).WithAlias(() => dto.Id)
16 .Select(o => customer.Name).WithAlias(() => dto.CustomerName)
17 .Select(o => o.OrderedDateTime).WithAlias(() => dto.OrderedDateTime)
18 .Select(o => o.Amount).WithAlias(() => dto.Amount)
19 )
20 .TransformUsing(Transformers.AliasToBean<OrderDTO>())
21 .OrderBy(o=>o.Amount).Desc
22 .Skip(pageIndex * pageSize).Take(pageSize)
23 .List<OrderDTO>();
24
25 transaction.Commit();
26 }
27
28 return retList;
29 }
2 {
3 OrderDTO dto = null;
4 Customer customer = null;
5
6 IEnumerable<OrderDTO> retList = null;
7
8 using (var session = NHibernateSession)
9 using (var transaction = session.BeginTransaction())
10 {
11
12 retList = session.QueryOver<Order>()
13 .JoinAlias(o => o.Customer, () => customer)
14 .SelectList(list => list
15 .Select(o => o.Id).WithAlias(() => dto.Id)
16 .Select(o => customer.Name).WithAlias(() => dto.CustomerName)
17 .Select(o => o.OrderedDateTime).WithAlias(() => dto.OrderedDateTime)
18 .Select(o => o.Amount).WithAlias(() => dto.Amount)
19 )
20 .TransformUsing(Transformers.AliasToBean<OrderDTO>())
21 .OrderBy(o=>o.Amount).Desc
22 .Skip(pageIndex * pageSize).Take(pageSize)
23 .List<OrderDTO>();
24
25 transaction.Commit();
26 }
27
28 return retList;
29 }
輸出的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)]
代碼說(shuō)明：
- 調(diào)用Skip()、Take()實(shí)現(xiàn)數(shù)據(jù)分頁(yè)讀取。
1 public int GetOrderCount()
2 {
3 using (var session = NHibernateSession)
4 using (var transaction = session.BeginTransaction())
5 {
6 int count = session.QueryOver<Order>()
7 .RowCount();
8
9 transaction.Commit();
10
11 return count;
12 }
13 }
2 {
3 using (var session = NHibernateSession)
4 using (var transaction = session.BeginTransaction())
5 {
6 int count = session.QueryOver<Order>()
7 .RowCount();
8
9 transaction.Commit();
10
11 return count;
12 }
13 }
輸出的SQL：
SELECT count(*) as y0_ FROM MyWorkShop_Order this_
代碼說(shuō)明：
- 調(diào)用RowCount()計(jì)算數(shù)據(jù)總量。
4. 子查詢(xún)（Subquery）
（1）查找金額最大的訂單
1 public Order GetMaxAmountOrder()
2 {
3 Order order = null;
4
5 using (var session = NHibernateSession)
6 using (var transaction = session.BeginTransaction())
7 {
8 var maxAmount = NHibernate.Criterion.QueryOver.Of<Order>()
9 .SelectList(a=>a.SelectMax(o=>o.Amount));
10
11 order = session.QueryOver<Order>()
12 .WithSubquery.WhereProperty(o => o.Amount).Eq(maxAmount)
13 .SingleOrDefault();
14
15 transaction.Commit();
16 }
17 return order;
18 }
2 {
3 Order order = null;
4
5 using (var session = NHibernateSession)
6 using (var transaction = session.BeginTransaction())
7 {
8 var maxAmount = NHibernate.Criterion.QueryOver.Of<Order>()
9 .SelectList(a=>a.SelectMax(o=>o.Amount));
10
11 order = session.QueryOver<Order>()
12 .WithSubquery.WhereProperty(o => o.Amount).Eq(maxAmount)
13 .SingleOrDefault();
14
15 transaction.Commit();
16 }
17 return order;
18 }
輸出的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_)
代碼說(shuō)明：
- .WithSubquery指定子查詢(xún)。
五、總結(jié)
見(jiàn)《NHibernate 3.x新功能實(shí)踐（一） QueryOver（上）》文章最后。
浙公網(wǎng)安備 33010602011771號(hào)