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?

Answer 1

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.