How to write a "Between" Query Expression for today's date and two date attributes on an entity in a single condition?

Question

I have a "new_Term" entity with a "new_StartDate" and "new_EndDate" attributes. I effectively want to write this statement in a single Query Expression:

new_StartDate <= DateTime.UtcNow <= new_EndDate

My first idea would be to use the Between ConditionOperator, but the between operator only works on a single attribute, ie:

// Not valid because the first parameter expects a string attribute name, not a value
new ConditionExpression(DateTime.UtcNow.Date, ConditionOperator.Between, "new_startdate", "new_enddate")

Besides the obvious two Condition Expressions, is there a way to do it in a single Condition Expression?

new ConditionExpression("new_startdate", ConditionOperator.LessEqual, DateTime.UtcNow);
new ConditionExpression("new_enddate", ConditionOperator.GreaterEqual, DateTime.UtcNow);

Answer 1

I believe that you have the best of all possible answers at the bottom of your question:

new ConditionExpression("new_startdate", ConditionOperator.LessEqual, DateTime.UtcNow);
new ConditionExpression("new_enddate", ConditionOperator.GreaterEqual, DateTime.UtcNow);

The ConditionOperator enumeration is the limiting factor -- whether you write fetchXml or use queryexpression you are stuck with the conidition operators available in this enumeration. And as you rightly pointed out, between doesn't take the parameters you would need.

Your only other supported (on-premise only) option to further reduce that statement is use SQL to access the filter views but that is certainly overkill :)