N+1 Issue

Topics: General, Writing modules
Mar 14, 2012 at 4:35 PM

Take the following snippet of code in a controller action:

var items = _eventRepository.Table
                .Take(100)
                .ToList();

            var eventItems = new List<EventLogItem>();

            foreach (var item in items)
            {
                var titlePart = _contentManager.Query(VersionOptions.Latest)
                    .Join<TitlePartRecord>()
                    .Where(t => t.ContentItemRecord.Id == item.ContentItemRecord.Id)
                    .List()
                    .FirstOrDefault()
                    .As<TitlePart>();

                eventItems.Add(new EventLogItem { EventRecord = item, TitlePart = titlePart });
            }

Here's the model:

public class EventRecord
    {
        public virtual int Id { get; set; }
        public virtual ContentItemRecord ContentItemRecord { get; set; }
        public virtual ContentItemVersionRecord ContentItemVersionRecord { get; set; }
        public virtual UserPartRecord User { get; set; }
        public virtual string Action { get; set; }
        public virtual DateTime? Date { get; set; }
       
    }

So basically I have a model that isn't an Orchard part, it just represents a record in the database (an event that occurred, such as "updated content item" or "deleted content item."

The query above basically selects the last 100 of these records.  It then loops through each of those items and grabs the TitlePart by doign a content query on titlepartrecord joining to the content item...I do this because the items (from _eventRepository.Table) can't be rendered as a part using .As<TitlePart> because it's a Record object, not a Content object.

Thus, the above query will do at its max, 1 query to get the event records, and then 100 queries more, one for each of the event items to get the title part.  There simply must be a better, more efficient way to handle this that I'm missing. Can someone enlighten me?

Mar 14, 2012 at 6:31 PM
Edited Mar 14, 2012 at 6:32 PM

Why don't you try GetMany? It is used in this tutorial

http://skywalkersoftwaredevelopment.net/blog/writing-an-orchard-webshop-module-from-scratch-part-7

Mar 15, 2012 at 2:53 PM

I'll read it over, thanks ravetam...

Mar 15, 2012 at 8:56 PM

GetMany seems nice, but it only works on items that implement IContent, and in my example above, EventRecord is just a record, not a part...so I can't do the GetMany to pull that data...

Mar 16, 2012 at 2:56 AM
Edited Mar 16, 2012 at 2:57 AM

This doesn't work?

 

var ids = items.Select(e=>e.ContentItemRecord.Id);

_orchardServices.ContentManager
	.GetMany<TitlePart>(ids, VersionOptions.Latest, QueryHints.Empty)
	.ToArray();

 

Or

Not sure this will work but worth a try

var titleParts = _contentManager.Query(VersionOptions.Latest)
                    .Join<TitlePartRecord>()
                    .Where(t => ids.Contains(t.ContentItemRecord.Id)
		    .ForPart<TitlePart>()
                    .List();

Mar 16, 2012 at 4:40 PM

The following code worked:

var ids = items.Select(e => e.ContentItemRecord.Id);
            var titleParts = _orchardService.ContentManager
                .GetMany<TitlePart>(ids, VersionOptions.Latest, QueryHints.Empty)
                .ToArray();

            var eventItems = new List<EventLogItem>();

            foreach (var item in items)
            {
                var titlePart = titleParts.Where(x => x.ContentItem.Id == item.Id).FirstOrDefault();
                eventItems.Add(new EventLogItem { EventRecord = item, TitlePart = titlePart });
            }

 

I'm not a huge fan of having to do a linq query to loop through the items again, but I don't know how you get away from that when you're inside a system like Orchard+NHIb...sometimes I long for the days of writing my own SQL and filling objects with a simple data mapper...

Coordinator
Mar 17, 2012 at 9:32 PM

Well, you can. You can grab the nHib session and just query in SQL. I did it in the OrchardPo module if you're looking for an example. That will of course tie you to a specific flavor of SQL. You can also use HQL.

Mar 19, 2012 at 2:15 PM

Yeah, I've done a little before, but it was a bit rough...I'll check out your module and see if your way feels a bit cleaner.  Thanks Bertrand...