Join several queries to optimise QueryOver query

Question

I am using NHibernate and while traversing my code I came upon two functions that are called in sequence. They are probably a school example of 1) extra database round trip and 2) in-memory processing at the application side. The code involved is:

 // 1) Getting the surveys in advance
 var surveys = DatabaseServices.Session.QueryOver<Survey>()
    .Where(x => x.AboutCompany.IsIn(companyAccounts.ToList()))

 // Actual query that can be optimized
 var unverifiedSurveys = DatabaseServices.Session.QueryOver<QuestionInSurvey>()
       .Where(x => x.Survey.IsIn(surveys.ToList()))
       .And(x => x.ApprovalStatus == status)
       .List();

 // 2) In-memory processing
 return unverifiedSurveys.Select(x => x.Survey).Distinct()
      .OrderByDescending(m => m.CreatedOn).ToList();

I have read that the actual Distinct() operation with the QueryOver API can be done using 1 .TransformUsing(Transformers.DistinctRootEntity)

Could anyone give an example how the queries can be combined thus having one round trip to the database and no application-side processing?

score 1 · Answer 1 · answered Nov 18 '13 at 17:47

This might be something like this, which requires a distinct projection of all properties of Survey. I guess there is a better solution, but can not get to it ;-) Hope this will help anyway.

Survey surveyAlias = null;

var result = 
session.QueryOver<QuestionInSurvey>()
    .JoinAlias(x => x.Survey, () => surveyAlias)
    .WhereRestrictionOn(() => surveyAlias.AboutCompany).IsIn(companyAccounts.ToList())
    .And(x => x.ApprovalStatus == status)
    .Select(
        Projections.Distinct(
        Projections.ProjectionList()
            .Add(Projections.Property(() => surveyAlias.Id))
            .Add(Projections.Property(() => surveyAlias.AboutCompany))
            .Add(Projections.Property(() => surveyAlias.CreatedOn))
        )
    )
    .OrderBy(Projections.Property(() => surveyAlias.CreatedOn)).Desc
    .TransformUsing(Transformers.AliasToBean<Survey>())
    .List<Survey>();

Thanks for the answer, it looks very nice. I haven't done any `Projections` before so this is interesting! I'll run the code later since I am off duty right now to check which one has the best SQL query :) — M. Mimpen, Nov 19 '13 at 09:37

score 1 · Accepted Answer · answered Nov 18 '13 at 17:49

1

The most suitable way in this scenario is to use Subselect. We will firstly create the detached query (which will be executed as a part of main query)

Survey survey = null;
QueryOver<Survey> surveys = QueryOver.Of<Survey>(() => survey)
    .Where(() => survey.AboutCompany.IsIn(companyAccounts.ToList()))
    .Select(Projections.Distinct(Projections.Property(() => survey.ID)));

So, what we have now is a statement, which will return the inner select. Now the main query:

QuestionInSurvey question = null;
var query = session.QueryOver<QuestionInSurvey>(() => question)
    .WithSubquery
    .WhereProperty(() => qeustion.Survey.ID) 
    .In(subQuery) // here we will fitler the results
   .And(() => question.ApprovalStatus == status)
   .List();

And what we get is the:

SELECT ...
FROM QuestionInSurvey
WHERE SurveyId IN (SELECT SurveyID FROM Survey ...)

So, in one trip to DB we will recieve all the data, which will be completely filtered on DB side... so we are provided with "distinct" set of values, which could be even paged (Take(), Skip())

answered Nov 18 '13 at 17:49

Radim Köhler

122,561
47
239
335

Thanks, the answers looks good. I prefer the SubQuery approach. Though you forgot some code at `.WithSubquery` I'll figure that out on my own.I'll run the code later since I am off duty right now to check which one has the best SQL query :) – M. Mimpen Nov 19 '13 at 09:38
1

Believe or not, the code is complete ;) the full documentation could be found here http://nhforge.org/doc/nh/en/index.html#queryqueryover-subqueries. Good luck with NHibernate ;) – Radim Köhler Nov 19 '13 at 09:40
Oh, sorry, you're completely right. I am kind of ashamed right now o_0 It's just that I write my code in a different way and didn't notice the `.In(subquery)`. My bad! – M. Mimpen Nov 19 '13 at 09:44
I guess, it is more "fluent" syntax. Hard to uncover (how to use that) but very good for reading ;) – Radim Köhler Nov 19 '13 at 09:45
Probably that's it. And one more question, why did you pick `Select(Projections.Distinct(Projections.Property(() => survey.ID)));` instead of `.TransformUsing(Transformers.xxx)`? – M. Mimpen Nov 19 '13 at 10:08
1

The Projection (if I did understand correctly the issue) is here to select the inner ID, the Survey ID. That will be used for filtering outer select `ID IN (....)`. In that case, there is no transformation, because it will be part of ON SQL statement... If I suggested that correctly ;) The Distinct statement is in fact redundant anyway... because it will be used for filtering, and DB engine will optimize it anyhow – Radim Köhler Nov 19 '13 at 10:15

Join several queries to optimise QueryOver query

2 Answers2

Linked