Как я могу воссоздать этот сложный SQL-запрос с помощью NHibernate QueryOver?

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

У нас есть много "праздничных" записей, которые связаны с посещением определенного жилья на определенную дату и т.д.

Я хотел бы извлечь из базы данных "лучший" праздник для каждого размещения (т.е. самую низкую цену), учитывая набор критериев поиска (например, продолжительность, аэропорт вылета и т.д.).

Будут несколько записей с одинаковой ценой, поэтому нам нужно выбрать сохранение предложения (по убыванию), затем по дате вылета по возрастанию.

Я могу написать SQL для этого, который выглядит так (я не говорю, что это самый оптимальный способ):

SELECT *
FROM Holiday h1 INNER JOIN (

    SELECT  h2.HolidayID,
        h2.AccommodationID,
        ROW_NUMBER() OVER (
            PARTITION BY h2.AccommodationID
            ORDER BY OfferSaving DESC
        ) AS RowNum
    FROM Holiday h2 INNER JOIN (

        SELECT  AccommodationID,
            MIN(price) as MinPrice
        FROM Holiday
        WHERE TradeNameID = 58001
        /*** Other Criteria Here ***/
        GROUP BY AccommodationID

    ) mp
    ON mp.AccommodationID = h2.AccommodationID
    AND mp.MinPrice = h2.price
    WHERE TradeNameID = 58001
    /*** Other Criteria Here ***/

) x on h1.HolidayID = x.HolidayID and x.RowNum = 1

Как вы можете видеть, это использует подзапрос в другом подзапросе.

Однако по нескольким причинам моим предпочтением было бы добиться этого же результата в NHibernate.

В идеале это будет сделано с QueryOver - причина заключается в том, что я динамически создаю критерии поиска, и это намного проще с беспроблемным интерфейсом QueryOver. (Я начал надеяться использовать NHibernate Linq, но, к сожалению, он недостаточно зрелый).

После больших усилий (будучи относительным новичком в NHibernate), я смог воссоздать самый внутренний запрос, который выбирает все номера и их минимальную цену.

public IEnumerable<HolidaySearchDataDto> CriteriaFindAccommodationFromPricesForOffers(IEnumerable<IHolidayFilter<PackageHoliday>> filters, int skip, int take, out bool hasMore)
    {
        IQueryOver<PackageHoliday, PackageHoliday> queryable = NHibernateSession.CurrentFor(NHibernateSession.DefaultFactoryKey).QueryOver<PackageHoliday>();

        queryable = queryable.Where(h => h.TradeNameId == website.TradeNameID);

        var accommodation = Null<Accommodation>();
        var accommodationUnit = Null<AccommodationUnit>();
        var dto = Null<HolidaySearchDataDto>();

        // Apply search criteria
        foreach (var filter in filters)
            queryable = filter.ApplyFilter(queryable, accommodationUnit, accommodation);

        var query1 = queryable

            .JoinQueryOver(h => h.AccommodationUnit, () => accommodationUnit)
            .JoinQueryOver(h => h.Accommodation, () => accommodation)
            .SelectList(hols => hols
                                    .SelectGroup(() => accommodation.Id).WithAlias(() => dto.AccommodationId)
                                    .SelectMin(h => h.Price).WithAlias(() => dto.Price)
            );

        var list = query1.OrderByAlias(() => dto.Price).Asc
            .Skip(skip).Take(take+1)
            .Cacheable().CacheMode(CacheMode.Normal).List<object[]>();

        // Cacheing doesn't work this way...
        /*.TransformUsing(Transformers.AliasToBean<HolidaySearchDataDto>())
        .Cacheable().CacheMode(CacheMode.Normal).List<HolidaySearchDataDto>();*/

        hasMore = list.Count() == take;

        var dtos = list.Take(take).Select(h => new HolidaySearchDataDto
                    {
                        AccommodationId = (string)h[0],
                        Price = (decimal)h[1],
                    });

        return dtos;
    }

Итак, мой вопрос...

Любые идеи о том, как добиться того, что я хочу использовать QueryOver, или, если необходимо, API-интерфейсы критериев?

Я бы предпочел не использовать HQL, но если это необходимо, я готов посмотреть, как это можно сделать с этим тоже (это затрудняет (или более беспорядочно) создавать критерии поиска).

Если это просто невозможно выполнить с помощью NHibernate, тогда я могу использовать SQL-запрос. В этом случае, мой вопрос заключается в том, может ли SQL быть улучшен/оптимизирован?

Ответ 1