Recently I needed a query with some computation made in SQL. But the difficulty was that the computation wasn't just an a + b statement. It was supposed to aggregate number of items underlying in my domain object with some extra conditions.

Suppose I have entity Order that looks like:

    public class Order
    {
        private List<OrderItem> items;

        public Order()
        {
            items = new List<OrderItem>();
        }

        public virtual int Id { get; protected set; }

        public virtual DateTime DateCreated { get; set; }

        public virtual IEnumerable<OrderItem> Items
        {
            get { return items; }
        }
    }

Then the OrderItem:

    public class OrderItem
    {
        public virtual int Id { get; protected set; }

        public virtual Article Article { get; set; }

        public virtual int Quantity { get; set; }

        public virtual decimal Price { get; set; }
    }

The Article property and Article entity are not important in this context so no sorce code for now.

What I needed exactly was: orders (OrderDto) created after date 2016-01-01 having at least one item that has the quantity greater than 100.0

The query (QueryOver) I needed looks like:

            var date = new DateTime(2016, 1, 1);
            var session = sessionFactory.GetCurrentSession();

            Order _this = null;
            OrderItem item = null;
            OrderDto dto = null;
            var result = session.QueryOver<Model.Order>(() => _this)
                .Left.JoinAlias(() => _this.Items, () => item,
                    NHibernate.Criterion.Restrictions.Gt(
                        NHibernate.Criterion.Projections.Property(() => item.Quantity),
                        100))
                .SelectList(l => l
                    .Select(() => _this.Id).WithAlias(() => dto.Id)
                    .Select(() => _this.DateCreated).WithAlias(() => dto.DateCreated)
                    .SelectGroup(() => _this.Id)
                    .SelectGroup(() => _this.DateCreated)
                    .SelectCount(() => item.Id).WithAlias(() => dto.ItemCount))
                .Where(
                    NHibernate.Criterion.Restrictions.Gt(
                        NHibernate.Criterion.Projections.Count(
                            NHibernate.Criterion.Projections.Property(() => item.Id)),
                        0))
                .Where(
                    NHibernate.Criterion.Restrictions.Gt(
                        NHibernate.Criterion.Projections.Property(() => _this.DateCreated),
                        date))
                .TransformUsing(
                    Transformers.AliasToBean<OrderDto>())
                .List();

Which resulted with SQL:

    SELECT
        this_.Id as y0_,
        this_.DateCreated as y1_,
        this_.Id as y2_,
        this_.DateCreated as y3_,
        count(item1_.Id) as y4_ 
    FROM
        "Order" this_ 
    left outer join
        "OrderItem" item1_ 
            on this_.Id=item1_.Order_id 
            and (
                item1_.Quantity > @p0 
            ) 
    WHERE
        this_.DateCreated > @p1 
    GROUP BY
        this_.Id,
        this_.DateCreated 
    HAVING
        count(item1_.Id) > @p2;

The SQL above looke pretty much what I needed.

The example shows how to compute some data with non trivial restrictions (conditions) in a query instead of post query computation in C#.