Group by with different select Nhibernate

Question

I want to do this SQL query in Nhibernate QueryOver:

SELECT id FROM tb_example
GROUP BY id, name
ORDER BY name

What I tried:

1) I added a .Select in SelectList trying to override the select of SelectGroup but no success

var query = session.QueryOver<Person>()
                   .SelectList(list => list
                   .Select(p => p.Id)
                   .SelectGroup(p => p.Id)
                   .SelectGroup(p => p.Name));
query = query.OrderBy(p => p.Name).Asc;

Generated SQL with query:

SELECT id, id, name FROM tb_example
GROUP BY id, name
ORDER BY name

2) Using query in results of another query. Like this:

SELECT id 
FROM 
  (SELECT id, name FROM tb_example
  GROUP BY id, name
  ORDER BY name)

But I don't know how to create a query of results from another query.

var groupByQuery = session.QueryOver<Person>()
                       .SelectList(list => list
                       .SelectGroup(p => p.Id)
                       .SelectGroup(p => p.Name));
var query = session.QueryOver<?????>();

How can I do this? Thanks!

Regarding #1, unfortunately with QueryOver you _must_ `SELECT` all of the grouping columns. #2 is completely impossible, as far as I know. QueryOver can only select from mapped tables (not arbitrary table expressions like your example shows). — Andrew Whitaker, Apr 25 '16 at 16:59
Also see this Q&A, might be a duplicate: http://stackoverflow.com/q/30913037/497356 — Andrew Whitaker, Apr 25 '16 at 17:05

score 0 · Answer 1 · answered Apr 25 '16 at 13:30

0

Simplest solution I know is:

var query = session
  .QueryOver<Person>()
  .SelectList(list => list
    .SelectGroup(p => p.Id)
    .SelectGroup(p => p.Name));
query = query.OrderBy(p => p.Name).Asc;
query.List().Select(x => (int)x[0]).ToList();

answered Apr 25 '16 at 13:30

Stefan Steinegger

63,782
15
129
193

Hi Stefan, thanks for anwsering. I can't execute the query and then use linq. With the result of this query I will use it in a .In. With a list or array, the 2100 parameters limitation will stop me. That's why I'm looking for a NHibernate solution. – Renan Araújo Apr 25 '16 at 13:43
1

I had the same problem a while ago. Can't remember how I solved it. Need to find it again. – Stefan Steinegger Apr 25 '16 at 14:10

score 0 · Answer 2 · answered Apr 25 '16 at 13:30

0

Can you try below example

  var groupByQuery =   (from p
        in this.Session.Query<Person>()
        group p by new { Id= p.Id, Name= p.Name}
        into g
        select p).ToList();

answered Apr 25 '16 at 13:30

Soner

1,280
3
16
40

Hi Soner, thanks for answering. I'm getting a compiler error in select p "The types argument cannot be inferred from the query". And I didn't find where you do the select only in p.Id, can you explain it a little? – Renan Araújo Apr 25 '16 at 13:52

Group by with different select Nhibernate

2 Answers2