How to get the latest/last record with a group by clause with NHibernate Linq provider

Question

I have used too much time (days) on this and I really hope someone can help me out. I found a good article on describing my problem in a generic way so let's stick to it.

I am trying to build this query but NHibernate fails to build the correct sql and returns a sql query exception.

Column vSagsAendring.Id is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. It could not execute the following query:

select
    viewsagsae0_.Id as Id155_,
    viewsagsae0_.SagId as SagId155_,
    viewsagsae0_.JournalNr as JournalNr155_,
    viewsagsae0_.LbfNr as LbfNr155_,
    viewsagsae0_.OrgNr as OrgNr155_,
    viewsagsae0_.OrgNavn as OrgNavn155_,
    viewsagsae0_.AfdNavn as AfdNavn155_,
    viewsagsae0_.SagsType as SagsType155_,
    viewsagsae0_.Status as Status155_,
    viewsagsae0_.SagsbehandlerInit as Sagsbeh10_155_,
    viewsagsae0_.Dato as Dato155_,
    viewsagsae0_.JournalAktionType as Journal12_155_,
    viewsagsae0_.Beskrivelse as Beskriv13_155_,
    viewsagsae0_.Ekstern as Ekstern155_
from vSagsAendring viewsagsae0_
group by viewsagsae0_.SagId

var query = from p in _session.Query<ViewSagsAendring>()
            group p by p.SagId
            into grp
            select grp.OrderByDescending(g => g.Dato).First();

This is another version also took from the article:

var query = from p in _session.Query<ViewSagsAendring>()
                    group p by p.SagId
                    into grp
                    let maxDato = grp.Max(g => g.Dato)
                    from p in grp
                    where p.Dato == maxDato
                    select p;

Martin Andersen Martin Andersen · Accepted Answer · 2013-12-05T08:11:09

It's have been a long journey, but now it's over. I hope that I can help someone else in the same situation by answering my own question.

var aendring = from sagsAendring in _session.Query<ViewSagsAendring>()
                       where sagsAendring.Dato ==
                       (
                          from innersagsAendring in _session.Query<ViewSagsAendring>()
                          where innersagsAendring.SagId == sagsAendring.SagId
                          select innersagsAendring.Dato
                       ).Max()
                       select sagsAendring;
var result = aendring.ToList();

And because you can chain linq statements you can build a linq filter like this

 if(Filters.VisInterneAendringer == false)
            query = query.Where(x => x.Ekstern == true);
 if (Filters.VisKunNyesteAendringer)
        {
            query = query.Where(sagsAendring => sagsAendring.Dato ==
                               (
                                   from innerSagsAendring in Session.Query<ViewSagsAendring>()                                                        where innerSagsAendring.SagId == sagsAendring.SagId
                                   select innerSagsAendring.Dato
                                ).Max());
        }

 return query;

How to get the latest/last record with a group by clause with NHibernate Linq provider

2 Answers