nHibernate Projections.Sum for joined columns

Question

I am creating a query with Criteria like this:

DetachedCriteria auftragCriteria = DetachedCriteria.For<Auftrag>("a");

I join multiple tables with:

DetachedCriteria positionJoin = auftragCriteria.CreateCriteria("a.Positionen", "p", JoinType.LeftOuterJoin);

And I use a projection to fill in my object SubTypeAuftrag

ProjectionList projectionListSubTypeAuftrag = Projections.ProjectionList();

Now I need to recreate the following sql code:

cast(sum(p.length * p.width / 1000) as decimal)

I tried the following:

projectionListSubTypeAuftrag.Add(Projections.Sum<Position>(p => p.length * p.width / 1000), "M1");

This leads to an error:

System.InvalidOperationException: 'variable 'p' of type 'xxx.Base.Position' referenced from scope '', but it is not defined'

I also tried:

        projectionListSubTypeAuftrag.Add(
            Projections.Cast(
                NHibernateUtil.Decimal,
                Projections.SqlProjection("p.length * p.width/ 1000 AS result", new[] { "result" }, new IType[] { NHibernateUtil.Double })
                ),
            "M1"
            );

How can I tell nHibernate where to find the length/width column?

Answer 1

Maybee this will point you in the right direction.

  var sqlMultiply = new VarArgsSQLFunction("(", "*", ")");
            var sqlDivide = new VarArgsSQLFunction("(", "/", ")");
            var multiplyLengthWidthProj = Projections.SqlFunction(sqlMultiply, NHibernateUtil.Decimal, Projections.Property(() => alias.Length), Projections.Property(() => alias.Width));

            var sumProjection = Projections.ProjectionList().Add(Projections.Sum(Projections.SqlFunction(sqlDivide, NHibernateUtil.Decimal, multiplyLengthWidthProj, Projections.Constant(1000))));