加入多个查询以优化QueryOver查询 [英] Join several queries to optimise QueryOver query

问题描述

我正在使用NHibernate，并且在遍历代码时遇到了两个依次调用的函数.它们可能是一个学校示例，其中包括1)额外的数据库往返和2)应用程序侧的内存中处理.涉及的代码是:

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

我有已阅读可以使用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?

推荐答案

在这种情况下，最合适的方法是使用Subselect.我们将首先创建独立查询(将作为主查询的一部分执行)

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

我们得到的是:

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

因此，在一次访问DB的过程中，我们将接收所有数据，这些数据将在DB端被完全过滤...因此，我们提供了独特"的一组值，甚至可以分页(Take()， Skip())

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

这篇关于加入多个查询以优化QueryOver查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！