Translating WITH ROLLUP SQL statement to LINQ

Question

I need a help to translate a statement from LINQ to SQL

SQL

SELECT MONTH(ind_receita.lad_ins_date) as 'Month', SUM(ind_receita.valor) as Monthly_Value
FROM ind_receita
WHERE YEAR(ind_receita.lad_ins_date) = 2014
GROUP BY MONTH(ind_receita.lad_ins_date)
WITH ROLLUP

I know how to do all the statement in LINQ but I have some doubt with the WITH ROLLUP

LINQ

var query = (from p in _repositorio.GetReceitas()
             where p.DataHoraCriacaoRegistro.Year == 2014
             group p by new { p.DataHoraCriacaoRegistro.Month } into grp
             select new ReceitaPorGrupoProduto
             {
                 // Column with the alias 'Monthly_Value'
                 ValorReceita = grp.Sum(p => p.Valor)

                 // Column with the alias 'Month'
                 DataHora = grp.Key.Month
             }).ToList();

But how can I put the WITH ROLLUP in my LINQ ?

A Rollup will give me just a totalizer, so, if we can do it without making a ROLLUP statement for LINQ, it's ok.

possible duplicate of [LINQ to SQL version of GROUP BY WITH ROLLUP](http://stackoverflow.com/questions/1343487/linq-to-sql-version-of-group-by-with-rollup) — crthompson, Sep 08 '14 at 16:49
@paqogomez I read this thread, but the solution was given in 2009, maybe a new solution can be done. Without construct a new class or method. — Lucas_Santos, Sep 08 '14 at 16:52
Why do you want to do this? I mean why not use raw SQL when you hit LINQ limitations? — Stilgar, Sep 08 '14 at 17:19
I'm using NHibernate, so to accomplish your suggestion, I need make a new generic method that can deal with raw SQL or isn't necessary ? — Lucas_Santos, Sep 08 '14 at 17:42

score 2 · Answer 1 · answered Feb 03 '17 at 09:04

Quite interesting solution is provided here

https://blogs.msdn.microsoft.com/mitsu/2007/12/21/playing-with-linq-grouping-groupbymany/

It describes how to perform groupbby by several properties. I.e:

var result = customers.GroupByMany(c => c.Country, c => c.City);

As result you would get hierarchical structure that can be simply converted to flat list. I hope you can adopt this for your case.

Translating WITH ROLLUP SQL statement to LINQ

1 Answers1