LINQ to NHibernate (3.0) : GroupBy and Sum in subquery gives NoTImplemented

Question

I have a Linq query using NHibernate 3.0. But it keeps returning an error.

threw exception: System.NotImplementedException: The method or operation is not implemented..

I tried the same in LINQ 2 SQL and it works perfectly.

What might be wrong here? Here is part of my select, it's a subquery with a Groupby and Sum.

  Amount = (System.Double)
  ((from m0 in _session.Query<Statement>()
  where m0.Code== c.Code
  group m0 by new
  {
      m0.Code
  }
  into g
  select new
  {
     Expr1 = (System.Double)g.Sum(p => p.Amount)
  }).First().Expr1)
  };

I have the latest CSR1 installed of NHibernate but it just doesn't seem to work with my query.

Answer 1

The LINQ provider in NH3 is currently in a beta state. There are certain constructs that are not yet supported. (The team plans to address this after the NH3 release.) The parts causing problems in your query are the "new {}" anonymous type in the group by clause and the First() in the context of a group by. Both are not currently implemented. The following query executes properly and should give the same results:

var query = from m0 in session.Query<Statement>()
            where m0.Code == c.Code
            group m0 by m0.Code into g
            select new {Expr1 = g.Sum(p => p.Amount)};
var result = query.ToList().First().Expr1;

First note that the "new {}" in the group by clause is not required. The other change was adding "ToList()". This forces the results to be queried from the database and then we use LINQ-to-Objects to get the First() result. The SQL generated for this query is:

select   cast(sum(statement0_.Amount) as DOUBLE PRECISION) as col_0_0_
from     Statement statement0_
where    (statement0_.Code is null)
         and ('FOO' /* @p0 */ is null)
          or statement0_.Code = 'FOO' /* @p0 */
group by statement0_.Code