Casting Nhibernate result into IDictionary<string,int>

Question

I am trying to convert the result of the query into IDictionary

Here string will contain orderId and the int will contain the TradedQuantity

The query below should join three objects Order, OrderRevision and OrderEvent. 1 Order can have many orderRevisions 1 OrderRevision can have many orderEvents

What the query is trying to do is to inner join three objects and get all order objects whose order id matches the list of orderids supplied to it. Then it does a group by based on orderId and gets the latest TradedQuantity from orderEvents object. LatestTradedQuantity will be the TradedQuantityFrom latest OrderEvent. For now the latest orderevent can be regarded as the one that has highest OrderEventId value.

OrderRevision revisionAlias = null;
Order orderAlias = null;

 var query =
            Session.QueryOver<OrderEvent>()
                .JoinAlias(oe => oe.OrderRevision,() => revisionAlias)
                .JoinAlias(oe => oe.OrderRevision.Order,() => orderAlias)
                .Where(x => x.OrderRevision.Order.SourceSystem.Name.ToLower() == sourceSystem.ToLower())
                .WhereRestrictionOn(x => x.OrderRevision.Order.Id).IsIn(orderIds.ToList())
                .SelectList(list => list.SelectGroup(x => x.OrderRevision.Order.SourceOrderIdentifier)
                    .SelectMax(x => x.Id).Select(x => x.TradedQuantity))
                .Select(x => new KeyValuePair<string, int?>(x.OrderRevision.Order.SourceOrderIdentifier, x.TradedQuantity)
                );

As this query does not do what is supposed to. Could you please help and let me know how the result can be cast into IDictionary?

Answer 1

You have tagged your question with linq-to-nhibernate, so I guess using it instead of queryover would suit you. With Linq, use a sub-query for selecting the "max" order events ids for each order, then query them and project them to a dictionary.

using System.Linq;
using NHibernate.Linq;

...

var orderEventsIdsQuery = Session.Query<OrderEvent>()
    .Where(oe => orderIds.Contains(oe.OrderRevision.Order.Id))
    .GroupBy(oe => oe.OrderRevision.Order.SourceOrderIdentifier,
        (soi, oes) => oes.Max(oe => oe.Id));

var result = Session.Query<OrderEvent>()
    .Where(oe => orderEventsIdsQuery.Contains(oe.Id))
    .ToDictionary(oe => oe.OrderRevision.Order.SourceOrderIdentifier,
        oe => oe.TradedQuantity);

This should do the job. I do not use QueryOver and I will not try to give an answer for doing it with QueryOver.