Join several queries to optimise QueryOver query

问题:

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?

回答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())

回答2:

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>();