Сложные запросы через ICriteria

Недавно пришлось сделать довольно таки интересный запрос с использованием NHibernate Criteria API. Думаю пример будет полезен в блоге. Итак рассмотрим следующую доменную модель:

 

Есть покупатель, у которого может быть много заказов, каждый из которых может содержать несколько продуктов.

Допустим, что необходимо построить страницу поиска, на которой можно запросить пользователей по следующим критериям:

1. Имя пользователя
2. Количество заказов
3. Общая сумма оплат по всем заказам

Для такого построения запроса я буду использовать следующий класс:

public class SearchCriteria
{
    public string UserName { get; set; }

    public int PageSize { get; set; }

    public int PageNumber { get; set; }

    public int? OrdersNumber { get; set;}

    public double? MinPayedMoney { get; set; }

    public double? MaxPayedMoney { get; set; }
}

Он содержит текущие условия поиска. Итак начнем. Самое простое что можно реализовать это поиск по имени пользователя:

Customer customerAlias = null;  // понадобится позже
var criteria = Global.CurrentSession.CreateCriteria(typeof(Customer), () => customerAlias);

if (!string.IsNullOrEmpty(searchCriteria.UserName))
{
    criteria.Add(SqlExpression.Like<Customer>(x => x.Name, searchCriteria.UserName, MatchMode.Anywhere));
}
criteria.SetMaxResults(searchCriteria.PageSize);
criteria.SetFirstResult(searchCriteria.PageSize * (searchCriteria.PageNumber - 1));

return criteria.List<Customer>();

Ничего интересного. Теперь добавим поиск по количеству товаров. Для этого необходимо будет использовать подзапрос. Делается это следующим образом:

if (searchCriteria.OrdersNumber.HasValue)
{
    ICriteria ordersCriteria = criteria.CreateCriteria<Customer>(x => x.Orders);

    DetachedCriteria computersCount = DetachedCriteria.For<CustomersOrder>();
    computersCount.SetProjection(Projections.RowCount());
    computersCount.Add<CustomersOrder>(x => x.Customer.Id == customerAlias.Id);

    ordersCriteria.Add(Subqueries.Eq(searchCriteria.OrdersNumber.Value, computersCount));
}

Для того, чтобы Nhibernate правильно выполнил join таблиц, мы используем customerAlias. В 6й строке мы указываем на то, что нас интересуют только заказы данного пользователя, а не общее количество заказов в таблице.

Подобным же образом необходимо сделать и запрос на общую сумму выплат:

DetachedCriteria payedAmout = DetachedCriteria.For<OrderLine>();
payedAmout.SetProjection(LambdaProjection.Sum<OrderLine>(x => x.TotalPrice));
payedAmout.CreateCriteria<OrderLine>(x => x.CustomersOrder)
    .Add<CustomersOrder>(x => x.Customer.Id == customerAlias.Id);

if (searchCriteria.MinPayedMoney.HasValue)
{
    criteria.Add(Subqueries.Lt(searchCriteria.MinPayedMoney, payedAmout));
}

if (searchCriteria.MaxPayedMoney.HasValue)
{
    criteria.Add(Subqueries.Gt(searchCriteria.MaxPayedMoney, payedAmout));
}

Собрав все воедино Nhibernate выполнит следующий SQL запрос:

SELECT top 20 this_.Id                as Id1_1_,
              this_.Name              as Name1_1_,
              customerso1_.Id         as Id3_0_,
              customerso1_.OrderDate  as OrderDate3_0_,
              customerso1_.CustomerId as CustomerId3_0_
FROM   Customers this_
       inner join CustomersOrders customerso1_
         on this_.Id = customerso1_.CustomerId
WHERE  this_.Name like '%test%' /* @p0 */
       and 2 /* @p1 */ = (SELECT count(* ) as y0_
                  FROM   CustomersOrders this_0_
                  WHERE  this_0_.CustomerId = this_.Id)
       and 100 /* @p2 */ < (SELECT sum(this_0_.TotalPrice) as y0_
                  FROM   OrderLines this_0_
                         inner join CustomersOrders customerso1_
                           on this_0_.CustomersOrderId = customerso1_.Id
                  WHERE  customerso1_.CustomerId = this_.Id)
       and 500 /* @p3 */ > (SELECT sum(this_0_.TotalPrice) as y0_
                  FROM   OrderLines this_0_
                         inner join CustomersOrders customerso1_
                           on this_0_.CustomersOrderId = customerso1_.Id
                  WHERE  customerso1_.CustomerId = this_.Id)

Обратите внимание на inner join в 7й строке. Если пользователь выполнил больше одного заказа, то в результате запроса SQL Server вернет столько строк, сколько заказов у пользователя. Чтобы избежать этого, необходимо добавить Distinct:

criteria.SetProjection(
                Projections.Distinct(
                    Projections.ProjectionList().Add(LambdaProjection.Property<Customer>(x => x.Id), "Id")
                                                .Add(LambdaProjection.Property<Customer>(x => x.Name), "Name")));

Теперь приведу весь код:

Customer customerAlias = null;  
var criteria = Global.CurrentSession.CreateCriteria(typeof(Customer), () => customerAlias);

if (!string.IsNullOrEmpty(searchCriteria.UserName))
{
    criteria.Add(SqlExpression.Like<Customer>(x => x.Name, searchCriteria.UserName, MatchMode.Anywhere));
}

if (searchCriteria.OrdersNumber.HasValue)
{
    ICriteria ordersCriteria = criteria.CreateCriteria<Customer>(x => x.Orders);

    DetachedCriteria computersCount = DetachedCriteria.For<CustomersOrder>();
    computersCount.SetProjection(Projections.RowCount());
    computersCount.Add<CustomersOrder>(x => x.Customer.Id == customerAlias.Id);

    ordersCriteria.Add(Subqueries.Eq(searchCriteria.OrdersNumber.Value, computersCount));
}

DetachedCriteria payedAmout = DetachedCriteria.For<OrderLine>();
payedAmout.SetProjection(LambdaProjection.Sum<OrderLine>(x => x.TotalPrice));
payedAmout.CreateCriteria<OrderLine>(x => x.CustomersOrder)
    .Add<CustomersOrder>(x => x.Customer.Id == customerAlias.Id);

if (searchCriteria.MinPayedMoney.HasValue)
{
    criteria.Add(Subqueries.Lt(searchCriteria.MinPayedMoney, payedAmout));
}

if (searchCriteria.MaxPayedMoney.HasValue)
{
    criteria.Add(Subqueries.Gt(searchCriteria.MaxPayedMoney, payedAmout));
}

criteria.SetProjection(
    Projections.Distinct(
        Projections.ProjectionList().Add(LambdaProjection.Property<Customer>(x => x.Id), "Id")
                                    .Add(LambdaProjection.Property<Customer>(x => x.Name), "Name")));

criteria.SetMaxResults(searchCriteria.PageSize);
criteria.SetFirstResult(searchCriteria.PageSize * (searchCriteria.PageNumber - 1));

criteria.SetResultTransformer(Transformers.AliasToBean<Customer>());

return criteria.List<Customer>();

Данный код формирует следующий SQL запрос:

SELECT distinct top 20 this_.Id   as y0_,
                       this_.Name as y1_
FROM   Customers this_
       inner join CustomersOrders customerso1_
         on this_.Id = customerso1_.CustomerId
WHERE  this_.Name like '%test%' /* @p0 */
       and 2 /* @p1 */ = (SELECT count(* ) as y0_
                  FROM   CustomersOrders this_0_
                  WHERE  this_0_.CustomerId = this_.Id)
       and 100 /* @p2 */ < (SELECT sum(this_0_.TotalPrice) as y0_
                  FROM   OrderLines this_0_
                         inner join CustomersOrders customerso1_
                           on this_0_.CustomersOrderId = customerso1_.Id
                  WHERE  customerso1_.CustomerId = this_.Id)
       and 500 /* @p3 */ > (SELECT sum(this_0_.TotalPrice) as y0_
                  FROM   OrderLines this_0_
                         inner join CustomersOrders customerso1_
                           on this_0_.CustomersOrderId = customerso1_.Id
                  WHERE  customerso1_.CustomerId = this_.Id)

Код выглядит как по мне страшновато. Но с другой стороны LINQ реализация врядли была бы проще. Все равно пришлось бы использовать синтаксис через extension методы и лямбда выражения.

Тут конечно можно еще кое что оптимизировать, но цель поста была показать как строить сложные запросы при помощи NHibernate. Надеюсь это поможет.