Are there any arithmetic operation projections in NHibernate?

Question

I would like to get this SQL from NHibernate:

SELECT SUM(color_pages) * SUM(total_pages)
FROM connector_log_entry
GROUP BY department_name

But I can't find any arithmetic operation (*) projections anywhere.

This is the code that I have so far:

Session.QueryOver<ConnectorLogEntry>()
       .SelectList(list => list
           .SelectGroup(m => m.DepartmentName)
           .WithAlias(() => dto.Department)
           .Select(Projections.Sum<ConnectorLogEntry>(m => m.TotalPages))
           //.Select(Projections.Sum<ConnectorLogEntry>(m => m.ColorPages))
           .WithAlias(() => dto.TotalColorPercentage))
       .TransformUsing(Transformers.AliasToBean<DepartmentConsumption>());

Even if you will be able to do it with ICriteria, hql query will be much more readable. — Sly, Jan 28 '11 at 14:04

score 8 · Accepted Answer · answered May 25 '12 at 14:51

Arithmetic operators can be used in criteria queries via the VarArgsSQLFunction SQL function. In your particular case, this would look something like:

Session.QueryOver<ConnectorLogEntry>()
    .SelectList(list =>
        list.SelectGroup(m => m.DepartmentName)
            .WithAlias(() => dto.Department)
            .Select(Projections.SqlFunction(
                new VarArgsSQLFunction("(", "*", ")"),
                NHibernateUtil.Int32,
                Projections.Sum<ConnectorLogEntry>(m => m.TotalPages),
                Projections.Sum<ConnectorLogEntry>(m => m.ColorPages)))
            .WithAlias(() => dto.TotalColorPercentage))
    .TransformUsing(Transformers.AliasToBean<DepartmentConsumption>());

This technique injects strings directly into the generated SQL, so you'll need to make sure the underlying database supports the operators you use.

I don't have a quick way to check if this solution is 100% good, but by looking at VarArgsSQLFunction class documentation I'm pretty sure that it is what I've been looking for more then year ago :) Hope this answer will help others in future. — Andrej Slivko, May 28 '12 at 15:47

Diego Mijelshon · Answer 2 · 2011-01-28T16:14:19.377

2

It's trivial with LINQ or HQL, but Criteria and QueryOver are not optimized for that (you have to use a SQL Projection)

HQL is almost the same as SQL:

select sum(ColorPages) * sum(TotalPages)
from ConnectorLogEntry
group by DepartmentName

LINQ is not hard either:

from entry in Session.Query<ConnectorLogEntry>()
group entry by entry.DepartmentName into g
select g.Sum(e => e.ColorPages) * g.Sum(e => e.TotalPages)

edited Jan 28 '11 at 16:14

answered Jan 28 '11 at 16:04

Diego Mijelshon

52,548
16
116
154

this query in my question is only small part. my real query is much more difficult then i wrote here, and i got it all with QueryOver already, moving all to hql or linq would be hard and problematic. – Andrej Slivko Jan 29 '11 at 17:32
Too bad. Use a SQL projection then. Good luck maintaining it. – Diego Mijelshon Jan 30 '11 at 02:13
What do you mean by "Criteria and QueryOver are not optimized for that"? Do you foresee any issues with writing a custom projection that would handle this for me? – csano Jun 28 '11 at 17:43
@j0k: You can certainly write a custom projection if you do that a lot with Criteria/QueryOver queries. But HQL is easier to read and write. – Diego Mijelshon Jun 28 '11 at 18:40

Are there any arithmetic operation projections in NHibernate?

2 Answers2

Linked