Best way to join Content Item (e.g. Event) with Bag or List items (Program)

[ColinRaaijmakers]

I have a content item "Event", which contains basic information about an event.
Then I have a Bag in the Event content item, which contains information about the programme, like date start, date and, availability, status.

How can I get a result of Events with Programs at once (joined), by using content manager or indexed queries?
I would like to show every program including event information on the page. I can't get this to work.

Also I have refactored this, by using a List within the Event content type, and add Program items to the List per Event.
But I can't get this to work eighter.

Can someone help me with this?

[hishamco]

If you have a content item containing a BagPart you can get the data at once when you call Orchard.GetContentItemByIdAsync(Id) then you can access your programs using contentItem.Content.Programs.ContentItems

[ColinRaaijmakers]

@hishamco Thank you for your reply. But I want to get the items in a joined query, like EF Core does.
Example:
var programs = await this.databaseContext.Set<Program>() .Include(x => x.Event) .Where(x => x.Start > DateTime.Now) .ToList();
But in OrchardCore I cannot reference or join bagparts or listparts when querying the database or contentmanager.
I'd like to join these information to show this on my page, but also to filter the page by date, label or tags selected on the program or event.

[hishamco]

To make it clear if you have a content item with attached BagPart you can access the bag items from the content item itself, but if you have a ListPart you can use ContainedPartIndex

FYI the blog theme is using ListPart https://github.com/OrchardCMS/OrchardCore/tree/main/src/OrchardCore.Themes/TheBlogTheme

[ColinRaaijmakers]

I want to join BagPart items or ListPart items in a query, not access them later through ContentItem.object, etc..

[hishamco]

As I told you before you can use ContainedPartIndex in your query

[sebastienros]

I agree with @hishamco here, you probably want to make Event to be a ListPart with Programme. The same way a Blog is a List of BlogPost. Then each Programme will be an individual content item that is indexed in ContentItemIndex table, and also in ContainedPartIndex (to filter or retrieve its Event).

To filter Programme by date you will also need the SQL Index feature which will map the fields into dedicated index tables. Or you can just use the CreatedUtc of any content item and then it will be available in the ContentItemIndex.