How to use content query API where clause on nested content items

2025/08/18 6:45 PM

I have a reusable content type called Event that has a field called EventRegion, this field is a content item selector to select a reusable content type called Region. As we know, in the database the EventRegion stores a JSON string of an array of the selected content item GUID's.

So the EventRegion field would contain data like this for example: [{"Identifier":"656eef94-63y1-4d2h-b0b5-05kdb4654342"}]


I want to do the following query to retrieve events that are in the region I selected:

var eventIds = (await executor.GetMappedResult<Entities.Event>(
    new ContentItemQueryBuilder().ForContentType(Entities.Event.CONTENT_TYPE_NAME, config => config
        .Where(where => where.WhereContains(nameof(Entities.Event.EventRegion), region.SystemFields.ContentItemGUID.ToString())) // improve this line
        .Columns("ContentItemID")
    ),
    options: new ContentQueryExecutionOptions { ForPreview = true },
    cancellationToken: cancellationToken
    ))
    .Select(e => e.SystemFields.ContentItemID)
    .ToList();

but using the WhereContains and converting the GUID to a string isnt the best route. Anyone have any ideas on how I can improve that code? Maybe a different content retriever API would be better?

EDIT:
What I'm trying to accomplish is retrieving events that are in the selected region. This code is within a widget and the widget has a ContentItemSelector field called Region that allows the user to select 1 content item of type Region. I then need to automatically pull in the next 3 upcoming events for that selected region. Events have a field for choosing the Region content item it belongs in. I don't need to region data for the Event I am trying to improve my Where clause in the query but the way linked content items are stored in the database is via a JSON string of the content item GUID.

Environment

  • Xperience by Kentico version: 30.8.2

  • .NET version: 8

Tags:
Kentico Community Portal Content querying Object querying Xperience Portal

Answers

2025/08/18 10:06 PM

I think you want to use the .Linking() Content item query method.

Your code would look something like this...

var eventIds = (await executor.GetMappedResult<Entities.Event>(
    new ContentItemQueryBuilder().ForContentType(Entities.Event.CONTENT_TYPE_NAME, config => config
        .Linking(nameof(Entities.Event.EventRegion), [region.SystemFields.ContentItemGUID])
        .Columns("ContentItemID")
    ),
    options: new ContentQueryExecutionOptions { ForPreview = true },
    cancellationToken: cancellationToken
    ))
    .Select(e => e.SystemFields.ContentItemID)
    .ToList();

The Linking() method here is saying "Give me all Event content items that link to the Region content items in the collection [region.SystemFields.ContentItemGUID] from the field Event.EventRegion.

However, I would recommend using the newer ContentRetriever API, which went from preview to production-ready in June's Refresh.

Using that API you can still perform the same .Linking() query but it will handle caching, mapping, preview context, etc... for you.

An additional note: You can avoid the awkward requirement to specify the namespace every time you refer to the Event type by not using a name for a content type that is likely going to be used by someone else's code (external library, .NET, ASPNET Core).

Instead give the Event content type name a bit more domain context and name it something like "BusinessEvent", "CorporateEvent", "RegionalEvent" (since each event is in a specific region(s)).

To response this discussion, you have to login first.