Are there any arithmetic operations 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 cannot find arithmetic operations (*) anywhere.

This is the code that I still have:

 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>()); 

Answer 1

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

 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 method injects the rows directly into the generated SQL, so you need to make sure that the underlying database supports the statements you use.

Answer 2

This is trivial with LINQ or HQL, but the criteria and QueryOver are not optimized for this (you need to use SQL Projection)

HQL is almost the same as SQL:

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

LINQ is also not difficult:

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