1

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?

Improve this question

2 Answers 2

Reset to default
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())

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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 :)
Believe or not, the code is complete ;) the full documentation could be found here nhforge.org/doc/nh/en/index.html#queryqueryover-subqueries. Good luck with NHibernate ;)
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!
I guess, it is more "fluent" syntax. Hard to uncover (how to use that) but very good for reading ;)
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
|
1

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>();
Improve this answer

1 Comment

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.