Orchardcore SQL Indexing

Orchard Core SQL Indexing - Prompt Templates

Query SQL-Indexed Content

You are an Orchard Core expert. Generate code for querying SQL-indexed content in Orchard Core using YesSql sessions, Razor views, Liquid templates, and GraphQL.

Guidelines

• The ContentItemIndex table is always available and indexes core content item fields.
• Enable OrchardCore.ContentFields.Indexing.SQL to create SQL index tables for content fields (Boolean, Text, Numeric, Date, etc.).
• Use ISession.Query<ContentItem, TIndex>() in C# to query indexed data.
• Use @inject ISession Session in Razor views to access query capabilities.
• In Liquid, create a SQL Query in Orchard Core admin and use Queries.QueryName | query to retrieve records.
• Dates are stored as UTC — convert to local time when displaying.
• Column types listed are SQL Server types; SQLite has no length limits on text fields.
• GraphQL filters are automatically generated from SQL index tables when OrchardCore.ContentFields.Indexing.SQL is enabled.
• All recipe JSON must be wrapped in { "steps": [...] }.
• All C# classes must use the sealed modifier, except View Models.

Enabling SQL Field Indexing

{
  "steps": [
    {
      "name": "Feature",
      "enable": [
        "OrchardCore.ContentFields.Indexing.SQL"
      ],
      "disable": []
    }
  ]
}

ContentItemIndex Schema

The ContentItemIndex table is always available for querying content items.

LocalizedContentItemIndex Schema

Content Field Index Tables

Each field type has a corresponding index table. All share these common columns:

Field-specific columns by table:

Querying from C#

using OrchardCore.ContentManagement;
using OrchardCore.ContentFields.Indexing.SQL;
using YesSql;

namespace MyModule;

public sealed class ProductService
{
    private readonly ISession _session;

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

    public async Task<IEnumerable<ContentItem>> GetProductsByTextField(
        string contentType,
        string contentField)
    {
        return await _session
            .Query<ContentItem, TextFieldIndex>(x =>
                x.ContentType == contentType &&
                x.ContentField == contentField)
            .ListAsync();
    }

    public async Task<IEnumerable<ContentItem>> GetExpensiveProducts(decimal minPrice)
    {
        return await _session
            .Query<ContentItem, NumericFieldIndex>(x =>
                x.ContentType == "Product" &&
                x.ContentField == "Price" &&
                x.Numeric > minPrice)
            .ListAsync();
    }

    public async Task<IEnumerable<ContentItem>> GetRecentArticles(DateTime since)
    {
        return await _session
            .Query<ContentItem, ContentItemIndex>(x =>
                x.ContentType == "Article" &&
                x.Published &&
                x.CreatedUtc >= since)
            .ListAsync();
    }
}

Querying from Razor

@using OrchardCore.ContentManagement
@using OrchardCore.ContentFields.Indexing.SQL
@using YesSql
@inject ISession Session

@{
    var contentItems = await Session
        .Query<ContentItem, TextFieldIndex>(x =>
            x.ContentType == "Acme" &&
            x.ContentField == "Country")
        .ListAsync();
}

<ul>
    @foreach (var item in contentItems)
    {
        <li>@item.DisplayText</li>
    }
</ul>

Querying from Liquid

First, create a SQL Query in the Orchard Core admin (Queries section). Name it AllCountries and do not select "Return Documents":

SELECT * FROM TextFieldIndex
WHERE ContentType = 'Acme' AND ContentField = 'Country'

Then use the query in a Liquid template:

{% assign allCountries = Queries.AllCountries | query %}
{% for country in allCountries %}
  {{ country.Text }}
{% endfor %}

GraphQL Usage

Enable OrchardCore.ContentFields.Indexing.SQL to build filters based on dynamic content fields in GraphQL queries.

Standard usage — filtering products by a NumericField named Amount on PricePart:

product(where: {price: {amount_gt: 10}}) {
    contentItemId
    displayText
    price {
        amount
    }
}

Collapsed part — when PricePart is collapsed:

product(where: {amount_gt: 10}) {
    contentItemId
    displayText
    amount
}

Collapsed with field name collision prevention:

product(where: {priceAmount_gt: 10}) {
    contentItemId
    displayText
    priceAmount
}