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?

Answer 1

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

1. 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.

2. 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.

3. 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_