Can I efficiently query generic fields without resorting to HQL?

Question

I find myself doing a lot of queries to fetch just the first couple of items of a big set, e.g. to show the three most recent news articles or blog posts on the homepage of a website.

As long as this query only involves predefined or custom Parts, I can do something like this:

public IEnumerable<ContentItem> GetTopArticles(int amount)
{
    var cultureRecord = _cultureManager.GetCultureByName(_orchardServices.WorkContext.CurrentCulture);

    var articles = _orchardServices.ContentManager.Query().ForType("Article")
        .Where<LocalizationPartRecord>(lpr => lpr.CultureId == cultureRecord.Id)
        .OrderBy<CommonPartRecord>(cpr => cpr.PublishedUtc)
        .Slice(0, amount);

    return articles;
}

I'm assuming this will more or less be the same as a SELECT TOP [amount] ... in SQL and will have good performance on a large number of records.

However, sometimes I use Migrations or Import to create Content Types from an external source and want to conditionally check a field from the generic Part. In this case I don't have a Part or PartRecord class that I can pass as a parameter to the ContentQuery methods and if I want to do a conditional check on any of the fields I currently do something like this:

public IEnumerable<ContentItem> GetTopArticles(int amount)
{
    var articles = _orchardServices.ContentManager.Query().ForType("Article")
        .OrderBy<CommonPartRecord>(cpr => cpr.PublishedUtc)
        .List()
        .Where(a => a.Content.Article.IsFeatured.Value == true)
        .Take(amount);

    return articles;
}

This is really wasteful and causes large overhead on big sets but I really, REALLY, do not want to delve into the database to figure out Orchard's inner workings and construct long and complex HQL queries every time I want to do something like this.

Is there any way to rewrite the second query with IContentQuery methods without incurring a large performance hit?

You may not want to, but HQL is the way to go. ContentManager is incapable of doing anything efficiently but the most basic queries. — Bertrand Le Roy
@BertrandLeRoy What about a string search on the Data field in the ContentItemVersionRecord? I know I shouldn't be this averse to learning HQL but technically that would be faster than going over large sets in memory right? — Lawyerson
Well, that would be even worse: string searches perform notoriously badly, and you'd have to rely on the serialization format for fields, and more generally on an implementation detail. That's extremely brittle. — Bertrand Le Roy
You could provide a customized content query / content manager class. I had an similar issue but without generic fields. Perhaps you can use my solution one way or the other: stackoverflow.com/a/38369053/3936440 — ViRuSTriNiTy

ub3rman123 ub3rman123 · Accepted Answer · 2016-12-22T13:42:37

I'm working on something similar (being able to query model data with a dynamic name). Sadly, I haven't found anything that makes it easy.

The method I've found that works is to do plain SQL queries against the database. Check out this module for syntax on that if you do later find yourself willing to delve into the database.

Can I efficiently query generic fields without resorting to HQL?

Is there any way to rewrite the second query with IContentQuery methods without incurring a large performance hit?

1 Answers