How to query field contents efficiently?

Topics: Writing modules
Oct 29, 2013 at 5:49 PM
Edited Oct 29, 2013 at 5:52 PM
Hi, so I wrote a module which needs to grab the values of a number of fields to dump out for some client-side code's consumption:
var query = _orchardServices.ContentManager.Query<CommonPart>(VersionOptions.Published, "ContentTypeName");

var items = query.List().Select(p => p.ContentItem.Parts.SelectMany(q => q.Fields)
    .Select(f => new {
        Name = f.Name,
        Value = f is MediaLibraryPickerField
            ? ((MediaLibraryPickerField) f).MediaParts.Select(m => m.MediaUrl).FirstOrDefault()
            : f.Storage.Get<string>(null),
Unfortunately, this results in an N+1 number of SQL statements (with 301 items in the database, it does 304 SQL statements).

The fields in question are all normal fields (in other words, attached to the common part (?)).

I've tried figuring out how to do the joins before doing the List() but it's kind of a hard slog. Can anyone give me some pointers? Thanks.
Oct 29, 2013 at 8:31 PM
Projections have to create index tables in order to query fields efficiently... This being said, yes, that code is not fantastic ;) Try instead to get the set of ids for the media content items, then do a GetMany with that.