3

I have a table:

Table { Id, Date, Number, Bool }

I need to group it by Number, select the row with max Date inside each group, and retrieve Id for each group. In the end I need to filter that to only have records that are !Bool. I am trying to do this with Linq Nhibernate.

This SQL seems to be doing what I want:

select Id from 
(select MAX(Date) as Dt, Number as N from Table group by Number) t, Table table
where table.Date = t.Dt and table.Number = t.N and table.Bool = 0

but turns out NHibernate does not allow for subqueries to be in from. How do I write this with Linq Nhibernate? It's also quite important for it to be efficient, so I would rather avoid having subqueries in select or where if they iterate over the whole set and (N+1) query problem.

The straightforward approach doesn't work either:

Session.Query<Table>().GroupBy(x => x.Number)
            .Select(x => x.Where(y => y.Date == x.Max(z => z.Date)))...
Archeg
  • 8,364
  • 7
  • 43
  • 90
  • Would you consider using the SQL query API feature of NHibernate rather than writing this using Linq? The SQL API provides full control over the statement run at the level of the database. – HashPsi Sep 09 '15 at 14:04
  • This is the last option I would choose. Our properties and db fields are often renamed and I am trying to minimize the hardcoded text influence in the code – Archeg Sep 09 '15 at 14:05
  • *There is a kind of similar issue http://stackoverflow.com/q/20528760/1679310 - it is QueryOver with subqueries, but it could give you some idea...* – Radim Köhler Sep 09 '15 at 14:08
  • @RadimKöhler Thanks for the tip, I will try this out, but it already frightens me how much complicated this is – Archeg Sep 09 '15 at 14:11
  • 2
    @Archeg I know.. but do you know what? with ORM you can NEVER use your own custom FROM. FROM always comes from mapping (usually ` – Radim Köhler Sep 09 '15 at 14:14
  • @RadimKöhler I was able to do it with a subquery in where, your link lead me to this solution, but the performance leaves much to be desired. It's more than 200 times slower. I will leave the question open for awhile, maybe somebody proposes a faster solution. Also I do not have any index over `Number` field as of now, maybe if I add it, it will perform much faster. Tnx for the help – Archeg Sep 09 '15 at 15:04

0 Answers0