NHibernate select complex sum

Question

I have an entity:

    class Entity
    {
        public int A { get; set; }
        public int B { get; set; }
        public int C { get; set; }
    }

I want to select sum of (A-B-C). So I want to run sql like this:

SELECT SUM(A-B-C) FROM Entity

I can achieve it by SqlProjection:

QueryOver.Of<Entity>().Select(Projections.SqlProjection("SUM(A-B-C) AS total", new[] { "total" }, new IType[] { NHibernateUtil.Int32 }));

But I do not want to use strings. How it can be done in other way?

score 2 · Accepted Answer · edited May 23 '17 at 10:30

Unfortunately NHibernate doesn't have built in arithmetic operators. Piggybacking on this question and answer, here are a few options:

Use VarArgsSQLFunction directly:

var subtractFunction = new VarArgsSQLFunction(string.Empty, " - ", string.Empty);

session.QueryOver<Entity>(() => entityAlias)
    .Select(
        Projections.Sum(
            Projections.SqlFunction(
                subtractFunction, NHibernateUtil.Int32,
                    Projections.Property(() => entityAlias.A),
                    Projections.Property(() => entityAlias.B),
                    Projections.Property(() => entityAlias.C)
            )
        )
    )
    .SingleOrDefault<int?>()

This is the most straightforward way to accomplish this, but there are a few ways to dress it up.

Create your own dialect and register a - function:

public class MyDialect : MsSql2008Dialect
{
    public MyDialect()
    {
        this.RegisterFunction("-", new VarArgsSQLFunction(string.Empty, " - ", string.Empty));
    }
}

session.QueryOver<Entity>(() => entityAlias)
    .Select(
        Projections.Sum(
            Projections.SqlFunction(
                "-", NHibernateUtil.Int32,
                    Projections.Property(() => entityAlias.A),
                    Projections.Property(() => entityAlias.B),
                    Projections.Property(() => entityAlias.C)
            )
        )
    )
    .SingleOrDefault<int?>()

This basically allows you to avoid redefining the - function every time you use it, and is a bit cleaner.

You can go even further and refactor the projection into an extension method:

public static class CustomProjections
{
    public static IProjection Subtract(IType type, params IProjection[] projections)
    {
        return Projections.SqlFunction("-", type, projections);
    }
}

session.QueryOver<Entity>(() => entityAlias)
    .Select(
        Projections.Sum(
            CustomProjections.Subtract(
                NHibernateUtil.Int32,
                Projections.Property(() => entityAlias.A),
                Projections.Property(() => entityAlias.B),
                Projections.Property(() => entityAlias.C)
            )
        )
    )
    .SingleOrDefault<int?>()

All of these generate the following SQL:

SELECT
    sum(this_.A - this_.B - this_.C) as y0_ 
FROM
    Entity this_

Thank you. Your solution is working for my case. But I guess that there is no way to handle sum with different operators(e.g SUM(A-B+C)). And it's strange because `Query().Sum(x=>x.A+x.B+x.C)` works. Unfortunately, I have to use QueryOver — Roman Koliada, Dec 28 '16 at 09:38
Well, you could define a `+` operator and then nest it with calls to the `-` one. But yeah, not ideal. — Andrew Whitaker, Dec 28 '16 at 14:24

NHibernate select complex sum

1 Answers1