Are there any arithmetic operation projections in NHibernate?
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.A开发者_运维知识库liasToBean<DepartmentConsumption>());
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.
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)
精彩评论