How to nest WHERE clause conditions using the Kentico Pages API without using raw SQL strings?

Question

I'd like to use the Kentico Pages API to recreate this SQL Where clause and to keep the parenthesis intact for altered order of operations:

WHERE ((EventEnd IS NULL OR EventEnd = '') AND EventStart < getdate()) 
     OR EventEnd < getdate()

Note: Do not rearrange this example to work properly without parenthesis because my question is about generating complex conditions with parenthesis using the Pages API.

This example is based on a theoretical Calendar Event Page Type that has:

• a required Start Date field,
• an optional End Date field.

The purpose of this WHERE clause is to filter Past Events by looking for a Start Date in the past if an end date value does not exist; otherwise it looks for the End Date value in the past.

However I cannot find good Kentico examples about how to achieve this nesting of conditions without dropping raw SQL strings into the pages query (which an be done).

Instead, I'm looking for a clever way to use .Where(..), .And(..), .Or(..) and other relevant API methods to generate the above WHERE clause with altered order of operations intact. The WhereBuilder class looks promising too, but doesn't seem to fit inline with the flow of a pages query.

Here's an example of a Pages query into which I'm trying to recreate the above WHERE clause without dropping in raw SQL strings for the conditions:

using CMS.DataEngine;
using CMS.DocumentEngine;
using CMS.Helpers;
using CMS.Base;

var pages = new TreeProvider().SelectNodes()
    .Published()
    .OnSite(MySiteName)
    .Types(CalendarEventPageType)
    .NestingLevel(1)
    .Path(calendarEventsPath, PathTypeEnum.Children)

 /* Looking for clever code after this line...
    How do I group these methods to get the desired order of operations with parenthesis?
   */

    .Where(..) 
    .And(..) 
    .Or(..);

Answer 1

You should be able to add a nested WhereCondition like so:

.Where(new WhereCondition()
        .WhereNull("EventEnd")
        .Or()
        .WhereEmpty("EventEnd")
        .And()
        .WhereLessThan("EventStart", DateTime.Now)
    )
.Or()
.WhereLessThan("EventEnd", DateTime.Now);

Reference Kentico docs https://docs.kentico.com/k11/custom-development/retrieving-database-data-using-objectquery-api#RetrievingdatabasedatausingObjectQueryAPI-Nestedwhereconditions