NHibernate中的QueryOver查询(No.2)

这个是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指定子查询。

Friday, June 01, 2012 | NHibernate

文章评论

  • # re: NHibernate中的QueryOver查询(No.2)
    请问,我现在联表查询,这是在on下,有条件,那这个程序怎么写
  • # re: NHibernate中的QueryOver查询(No.2)
    并不很明白你的意思。
    JOIN做查询,ON下是链表查询的关联条件,如果有其他条件,可以使用Where子句来添加即可

发表评论

Please add 8 and 2 and type the answer here:

关于博主

  一枚成分复杂的网络IT分子,常年游弋于电子商务,属于互联网行业分类中的杂牌军。当前正在待业中...