How to group by and order by, with LINQ, NHibernate and uNHAddins

Question

We're building a WPF application, using Oracle database, also using NHibernate and uNHAddins extensions. In a DataGrid, we're trying to get values from a table, with this query LINQ:

return (from f in rFConsumption.GetAll()
                let d = f.CounterDtm
                where f.CounterDtm >= dataHoraInicio && f.CounterDtm <= dataHoraFim
                group f by (d.Year - 2000) * 384 + d.Month * 32 + d.Day into g
                select new RFConsumption
                {                       
                    COGCounter1 = (g.Sum(f => f.COGCounter1)),
                    BFCounter1 = (g.Sum(f => f.BFCounter1)),
                    NatGasCounter1 = (g.Sum(f => f.NatGasCounter1)),
                    MixGasCounter1 = (g.Sum(f => f.MixGasCounter1)),
                    COGCounter2 = (g.Sum(f => f.COGCounter2)),
                    BFCounter2 = (g.Sum(f => f.BFCounter2)),
                    NatGasCounter2 = (g.Sum(f => f.NatGasCounter2)),
                    MixGasCounter2 = (g.Sum(f => f.MixGasCounter2)),
                    COGCounter3 = (g.Sum(f => f.COGCounter3)),
                    BFCounter3 = (g.Sum(f => f.BFCounter3)),
                    NatGasCounter3 = (g.Sum(f => f.NatGasCounter3)),
                    MixGasCounter3 = (g.Sum(f => f.MixGasCounter3)),
                }
                ).ToList<RFConsumption>();

So, my question is:

How I do to use group by, with order by, using NHibernate;

How can i make group by return date data field to that specified group.

Mahmoud Gamal · Accepted Answer · 2011-09-21T07:25:53.393

You can write the same query with NHibernate with several ways, the most interesting one for me really is the NHibernate QueryOver<>. So, if your query works fine then, this query should work:

 return Session.QueryOver<rFConsumption>()
            .Where( fc => (fc.CounterDtm >= dataHoraInicio && fc.CounterDtm <= dataHoraFim))
            .SelectList(list => list
                .SelectGroup(f => ((f.CounterDtm.Year - 2000) * 384 + f.CounterDtm.Month * 32 + f.CounterDtm.Day)) //use group by
                .Select(exp =>
                    new RFConsumption()   //here you define the return data type based on your specified group
                    {
                        // exp[0] represents the data returned and grouped by the above statements, so here you can reform it to fit into the form of your new entity
                        // exp[0] here will be equivilant to g in your query
                    })
                    .OrderBy( ee => ee.COGCounter1 ) //order by any of the properties of RFConsumption
                    .ToList<RFConsumption>();

you should first add the entity RFConsumption:

public calss RFConsumption
{
     public int COGCounter1  { get; set; }
     public int BFCounter { get; set; } 
     ....
}

Thanks for you answer, but i've forgot to tell something. We're using MVVM, and NHibernate it's in the Layer Service. This is what rFConsumption.GetAll() runs: public IQueryable GetAll() { return (from t in Session.Query() select t); } How i do modify this? Thanks again. — Gustavo Gonçalves, Sep 21 '11 at 14:52
Ok, i've changed the Service Layer and implemented the IQueryOver Find(). Now, my query is: ---- return (from f in rFConsumption.Find() where( fc => ((fc.CounterDtm >= dataHoraInicio) && (fc.CounterDtm <= dataHoraFim)).SelectList(list => list).SelectGroup(g => ((g.CounterDtm.Year - 2000) * 384 + g.CounterDtm.Month * 32 + g.CounterDtm.Day).Select(exp => new RFConsumption() { ... }) .OrderBy( ee => ee.COGCounter1 )) .ToList()); - Errors: into fc -> Cannot convert lambda expression to type 'bool' because it is not a delegate type. — Gustavo Gonçalves, Sep 22 '11 at 12:39

How to group by and order by, with LINQ, NHibernate and uNHAddins

1 Answers1