Eager Loading

Topics: Customizing Orchard, General, Writing modules
Sep 30, 2012 at 9:48 PM
Edited Sep 30, 2012 at 10:26 PM

Dear all,

Is there a simple way, knowing that we cannot use .FetchMany().ThenFetchMany, to write the following EF code in a NHibernate way (the code is very simple, eager loads the entities, some calculations and then some properties are modified and saved to the db):


        private BrokerageAccount UpdateAccount(string acctNumber)

            BrokerageAccount acct = null;
            acct = DataContext.BrokerageAccounts
                .Include("Positions.Security").SingleOrDefault(ba => ba.AccountNumber == acctNumber);

            if (acct != null && acct.Positions != null)
                acct.Positions = acct.Positions.OrderBy(p => p.Total).ToList();

                //Get account position securities
                var securities = acct.Positions.Select(p => p.Security).Distinct().ToList();

                var positions = acct.Positions;
                foreach (var pos in positions)
                    pos.Total = pos.Shares * pos.Security.Last;
                    DataContext.Entry(pos).State = System.Data.EntityState.Modified;
                acct.PositionsTotal = acct.Positions.Sum(p => p.Total);
                acct.Total = acct.PositionsTotal + acct.CashTotal;

                DataContext.Entry(acct).State = System.Data.EntityState.Modified;

                    //If we fail simply continue for sample app

            return acct;
Question; which Orchard methods could we use to make the above mentioned calculations? Use .SetFetchMode, QueryHints or other methods? And how?

Sep 30, 2012 at 9:51 PM

The last button on the CodePlex editor enables you to insert some properly formatted C# into your messages. Can you please try again with this?

Oct 1, 2012 at 8:48 AM
Edited Oct 1, 2012 at 8:49 AM

Can someone contribute to this issue? In the case of upgrading to NHibernate 3.0, could we use the renewed linq provider? 

Oct 2, 2012 at 1:41 AM

So the question is how to do eager loading in Orchard? If so, you do that by specifying QueryHints (look for usage of this).

Oct 2, 2012 at 9:42 PM
Edited Oct 2, 2012 at 10:00 PM

Dear all,

Could someone please write a blog/tutorial on QueryHints and the use of Eager Loading in Orchard. Especially to calculate mathematically a property using several properties from several entities (domain models).

For example, first: [[ property (Xentity)total1 = ((Yentity)Price * (Zentity)*VAT) + (Ventity)Number ]]. Second: [[ property (Xentity)total2 = ((Yentity)Price * (Zentity)*VAT) + (Ventity)Number ]].

And then save/update these two (total) outcomes. So we have related objects, 4 entities (Xentity, Yentity, Zentity and Ventity).

According to Bernard you'll have to use QueryHints, but HOW?

Oct 2, 2012 at 11:09 PM

Hey Bernard, do it !

Oct 2, 2012 at 11:20 PM

Look for usage in the existing code and figure it out. Who's Bernard?

Oct 3, 2012 at 7:11 PM
Edited Oct 3, 2012 at 7:15 PM

Merci/Thanks Bernard. 

I'll wait patiently the results (a blog of tutorial on the QueryHints issue cracking the above mentioned case). I myself couldn't understand (figure out) the usage of QueryHints in the core Orchard code and I think the majority of the Orchard (community) members have the same feeling. What the hell is QueryHints and foremost, how to use this beast (in mathematical operations (see above)).

Oct 3, 2012 at 8:18 PM

Seriously, who's Bernard?

Oct 3, 2012 at 10:10 PM

Here is a snippit from BlogPostService.cs if you cant find one. They seem quite straight forward.

IContentQuery<ContentItem, CommonPartRecord> GetBlogQuery(ContentPart<BlogPartRecord> blog, VersionOptions versionOptions) { return _contentManager.Query(versionOptions, "BlogPost") .Join<CommonPartRecord>().Where( cr => cr.Container == blog.Record.ContentItemRecord).OrderByDescending(cr => cr.CreatedUtc) .WithQueryHintsFor("BlogPost") ; }

Oct 4, 2012 at 10:36 AM
Edited Oct 4, 2012 at 10:37 AM


I understand the usage of it in general. But in the case of operations and then saving an item and then using the saved item into a next mathematical operation (with several properties from several entities/models (see the above mentioned case)) and then save/update all.

Please a tutorial on these issues. 

Thanks, Bertrand (and NOT Bernard).


Oct 4, 2012 at 5:03 PM


Sorry, but I'am a beginner on Orchard, I would like to understand correctly, what is the purpose to use QueryHints.

I saw some use of QueryHint inside the class C:\Orchard\src\Orchard\ContentManagement\DefaultContentManager.cs

Is it the same as http://msdn.microsoft.com/en-us/library/ms187713.aspx but for nHibernate?

A kind of optimization, I dont find anything about "queryHint + c#" in google. (http://stackoverflow.com/questions/5158102/force-hint-to-index-from-entity-framework-to-sql-server)

"Hints are options or strategies specified for enforcement by the SQL Server query processor on SELECT, INSERT, UPDATE, or DELETE statements. The hints override any execution plan the query optimizer might select for a query."

So Am I right is is a kind of optimization before the query threated by nHibernate?


Oct 4, 2012 at 7:30 PM

To avoid select n+1 issues, you instruct the system that you will be using some specific part, for example, so the the first query brings them in on the first query instead of creating additional queries later, when the part is actually being used.

Oct 4, 2012 at 8:50 PM
Edited Oct 4, 2012 at 8:51 PM

Bertrand, you really confused me with this. Could you first write code (implementing QueryHints) and then explain that code; like mathematics ==> a professor explains everything using symbols and not text.

Please explain it, or a member of the Orchard community, thoroughly. 

Oct 10, 2012 at 3:58 AM

Oh, gee. I'm so sorry I confused you, but I don't have time to write code samples for every question on these forums, unfortunately. Here are a few things you could have done. If you don't know what "select n+1" means, you could have googled it: https://www.google.com/search?btnG=1&pws=0&q=select+n%2B1

Let me explain in the context of Orchard. If you naively query the database for a list of items, you're first going to bring back the content item record, and then as parts are needed, you're going to implicitly hit the database again, once for each item. n items, n+1 hits where there could have been just one. That gets expensive very fast. If you have additional relationships, that only makes it worse. So if you had looked for the QueryHint class, you would have seen its members, such as ExpandRecords and ExpandParts. If you had then looked for usage of these members, you would have found a few classes, in particular DefaultContentQuery, which implements some additional helpers, like WithQueryHintsFor. Coincidentally, you could have seen an example of usage of this method a few posts above, in Nick's post specifically, but even if you missed that, you could have found usage of this method and traced it to a number of interesting places such as WidgetService. Here's what it does with it:

        public IEnumerable<WidgetPart> GetWidgets() {
            return _contentManager
                .Query<WidgetPart, WidgetPartRecord>()
                .WithQueryHints(new QueryHints().ExpandRecords<CommonPartRecord>()).

This simply brings the list of all widgets, making sure the common part is brought along with the first request, because we know that we are going to need it.

In other words, query hints are for giving Orchard hints about what records we know we are going to need, so that it can bring them in sooner rather than with later discrete calls.

I agree that it would be great if someone could write a doc topic on this. The documentation is on GitHub, and we're taking contributions.

Oct 10, 2012 at 4:16 AM

Now of course, you may need to drop to the level of nHibernate if you're doing queries that are not through the content manager but at a lower level.