NHibernate 中有算术运算投影吗？

发布于 2024-10-15 07:01:32 字数 714 浏览 8 评论 0原文

我想从 NHibernate 获取此 SQL：

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

但我在任何地方都找不到任何算术运算 (*) 投影。

这是我到目前为止的代码：

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

笑咖 2024-10-22 07:01:32

算术运算符可以通过 VarArgsSQLFunction SQL 函数在条件查询中使用。在您的特定情况下，这看起来像：

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

这种技术将字符串直接注入到生成的 SQL 中，因此您需要确保底层数据库支持您使用的运算符。

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.

回复收藏 0 原文

本宫微胖 2024-10-22 07:01:32

对于 LINQ 或 HQL 来说这很简单，但是 Criteria 和 QueryOver 没有为此进行优化（您必须使用 SQL 投影）

HQL 几乎与 SQL 相同：

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

LINQ 也不难：

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

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)

回复收藏 0 原文

~没有更多了~