orchardcore-content-queries

Orchard Core Content Queries - Prompt Templates

Query Content Items

You are an Orchard Core expert. Generate code for querying content items using YesSql indexes and IContentManager.

Guidelines

• Orchard Core uses YesSql as its document database abstraction over SQL.
• ISession is the primary interface for querying YesSql indexes.
• ContentItemIndex is the built-in index for all content items.
• Custom indexes can be created for frequently queried fields.
• IContentManager provides higher-level content operations (Get, New, Create, Publish).
• Always use async/await patterns for database queries.
• Use .With<IndexType>() to join against specific indexes.
• Keep YesSql predicates translatable: use comparisons, null checks, and boolean && / ||, but avoid ternaries and other conditional expressions inside query lambdas.
• If null and non-null rows need different query logic, split the query into multiple supported YesSql expressions and combine the results in memory.
• Use literal column names in CreateMapIndexTableAsync() and AlterIndexTableAsync() for custom YesSql migrations; do not use nameof(...).
• MapIndex tables already include the DocumentId column automatically, so do not add it manually in the migration.
• Always seal classes.

Querying with ContentItemIndex

using OrchardCore.ContentManagement;
using OrchardCore.ContentManagement.Records;
using YesSql;

public sealed class ContentQueryService
{
    private readonly ISession _session;

    public ContentQueryService(ISession session)
    {
        _session = session;
    }

    // Query by content type
    public async Task<IEnumerable<ContentItem>> GetByTypeAsync(string contentType)
    {
        return await _session
            .Query<ContentItem, ContentItemIndex>(x => x.ContentType == contentType && x.Published)
            .ListAsync();
    }

    // Query by content type with paging
    public async Task<IEnumerable<ContentItem>> GetPagedAsync(string contentType, int page, int pageSize)
    {
        return await _session
            .Query<ContentItem, ContentItemIndex>(x => x.ContentType == contentType && x.Published)
            .OrderByDescending(x => x.CreatedUtc)
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .ListAsync();
    }

    // Query by display text
    public async Task<ContentItem> GetByDisplayTextAsync(string displayText)
    {
        return await _session
            .Query<ContentItem, ContentItemIndex>(x => x.DisplayText == displayText && x.Published)
            .FirstOrDefaultAsync();
    }

    // Count content items
    public async Task<int> CountByTypeAsync(string contentType)
    {
        return await _session
            .Query<ContentItem, ContentItemIndex>(x => x.ContentType == contentType && x.Published)
            .CountAsync();
    }

    // Query latest versions (including drafts)
    public async Task<IEnumerable<ContentItem>> GetLatestAsync(string contentType)
    {
        return await _session
            .Query<ContentItem, ContentItemIndex>(x => x.ContentType == contentType && x.Latest)
            .ListAsync();
    }
}