QueryOver, GroupBy and Subselect

Question

how can I do this with queryover and a subquery?

FakturaObjektGutschrift fog = null;
int[] idS = Session.QueryOver(() => fog)
            .SelectList( list => list
                .SelectMax(() => fog.Id)
                .SelectGroup(() => fog.SammelKontierung)   
             ).List<object[]>().Select(x => (int)x[0]).ToArray();

And using the ids in another query

IQueryOver<Beleg, Beleg> sdfsd = Session.QueryOver(() => bGut)
    .AndRestrictionOn(() => fog.Id).IsIn(idS); //iDs is a list of int.

I would like do this with a subquery because there is a limitation on the number of parameters for a SQL query. How do I do this?

How do I write the first query, but without selecting the SelectGroup()? This is exactly where I got stuck.

score 2 · Accepted Answer · edited May 23 '17 at 12:32

2

Group by without projection in QueryOver API is currently not supported link.

You can use LINQ to create the projection in a subquery:

 var subquery = Session.Query<FakturaObjektGutschrift>()
     .GroupBy(x => x.SammelKontierung)
     .Select(x => x.Max(y => y.Id));

 var query = Session.Query<Beleg>()
     .Where(x => subquery.Contains(x.Id));

If you really needs QueryOver to create more complex queries check this solution link.

edited May 23 '17 at 12:32

Community

1
1

answered Jul 28 '15 at 16:11

Najera

2,869
3
28
52

Thanks a lot for your answer! My workaround: i created an additional db-view, mapped it and working with a subquery which does not contain a groupby. – yonexbat Jul 29 '15 at 06:53

QueryOver, GroupBy and Subselect

1 Answers1