Question

Filter group in C#

01:06 Nov 18, 2020

I am just wondering the syntax for doing nested filters in C# ESQ. I looked through documentation and academy, but I was not able to find anything. I want a filter that has a AND and then a nested OR.

Please reference the pseudo-code

(CreatedOn > ThreeHoursAgo && ( Status === "Complete" || Status === "Pending" ))

Code I have so far

var esqFirstFilter = esq.CreateFilterWithParameters(FilterComparisonType.GreaterOrEqual, "CreatedOn", ThreeHoursAgo);
 
esq.Filters.Add(esqFirstFilter);

2 comments

Best reply

Ryan Farley

01:51 Nov 18, 2020

IIRC, you need to create an EntitySchemaQueryFilterCollection and then populate it with another EntitySchemaQueryFilterCollection for the nested Or. Something like this, but going off top of my head - hopefully this gets you close:

// create filter collection with And
var filterGroup = new EntitySchemaQueryFilterCollection(esq, LogicalOperationStrict.And);
 
// add CreatedOn filter
filterGroup.Add(esq.CreateFilterWithParameters(FilterComparisonType.GreaterOrEqual, "CreatedOn", ThreeHoursAgo));
 
// add another filter collection for Status with Or
filterGroup.Add(new EntitySchemaQueryFilterCollection(esq, LogicalOperationStrict.Or) {
    esq.CreateFilterWithParameters(FilterComparisonType.Equal, "Status.Text", "Complete"),
    esq.CreateFilterWithParameters(FilterComparisonType.Equal, "Status.Text", "Pending")
});
 
// now add filter collection to esq
esq.Filters.Add(filterGroup);

Ryan

Ryan Farley

01:51 Nov 18, 2020

// create filter collection with And
var filterGroup = new EntitySchemaQueryFilterCollection(esq, LogicalOperationStrict.And);
 
// add CreatedOn filter
filterGroup.Add(esq.CreateFilterWithParameters(FilterComparisonType.GreaterOrEqual, "CreatedOn", ThreeHoursAgo));
 
// add another filter collection for Status with Or
filterGroup.Add(new EntitySchemaQueryFilterCollection(esq, LogicalOperationStrict.Or) {
    esq.CreateFilterWithParameters(FilterComparisonType.Equal, "Status.Text", "Complete"),
    esq.CreateFilterWithParameters(FilterComparisonType.Equal, "Status.Text", "Pending")
});
 
// now add filter collection to esq
esq.Filters.Add(filterGroup);

Ryan

Kirill Krylov CPA

09:31 Nov 24, 2020

EntitySchemaQuery esqResult = new EntitySchemaQuery(userConnection.EntitySchemaManager, "YourEntityHere");
 
esqResult.AddColumn("CreatedOn ");
esqResult.AddColumn("Status ");
 
Guid StatusComplete = Guid.Parse("XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"); // insert your guid
Guid StatusPending = Guid.Parse("XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"); // insert your guid
 
//Status Filter
esqResult.Filters.Add(
    new EntitySchemaQueryFilterCollection(
        esqResult,
        Terrasoft.Common.LogicalOperationStrict.Or,
        esqResult.CreateFilterWithParameters(FilterComparisonType.Equal, "Status", StatusComplete),
        esqResult.CreateFilterWithParameters(FilterComparisonType.Equal, "Status", StatusPending)
        )
    );
 
//Date Filter
TimeZoneInfo userTimeZonInfo = userConnection.CurrentUser.TimeZone;
DateTime createdOn = TimeZoneInfo.ConvertTimeToUtc(activity.GetTypedColumnValue&lt;DateTime&gt;("CreatedOn"), userTimeZonInfo);
DateTime ThreeHoursAgo = DateTime.Now.AddHours(-3);
 
 
var dateFilter = new EntitySchemaQueryFilterCollection(
    esqResult,
    Terrasoft.Common.LogicalOperationStrict.And,
    esqResult.CreateFilterWithParameters(FilterComparisonType.GreaterOrEqual, "CreatedOn", ThreeHoursAgo)
);
 
esqResult.Filters.Add(
    new EntitySchemaQueryFilterCollection(
        esqResult,
        Terrasoft.Common.LogicalOperationStrict.And,
        dateFilter1,
    )
);
EntityCollection entitites = esqResult.GetEntityCollection(userConnection);

Kirill

Show all comments