Hi, I’m having trouble creating a predicate for the following select (Oracle):

select * from person where to_number(to_char(date_of_birth,'MM')) between 1 and 4

Here's what I've started with:

IEntityField2 monthField = PersonFields.DateOfBirth; monthField.ExpressionToApply = new Expression( new DbFunctionCall("TO_CHAR", new object[] { PersonFields.DateOfBirth, "MM" }),ExOp.Equal,1);

Am I on the right track? If so how do I add the expression to the RelationPredicateBucket?

Thanks

Hi KG, Welcome to the forums

Here is an approximate/untested code of what you want to achieve.

// collection to fetch
EntityCollection<PersonEntity> people = new EntityCollection<PersonEntity>(new PersonEntityFactory());

// function calls                       
DbFunctionCall toCharFunction = new DbFunctionCall("TO_CHAR ({0}, 'MM')", new object[] { PersonFields.DateOfBirth});
DbFunctionCall toNumberFunction = new DbFunctionCall("TO_NUMBER", new object[] { toCharFunction });

/// here please select a NUMERIC field, so the DQE could deal with BETWEEN values as NUMERIC
EntityField2 expressionToFilter = PersonFields.PersonId.SetExpression(toNumberFunction);

/// shorter version of above lines:
/// ---
/// EntityField2 expressionToFilter = PersonFields.PersonId.SetExpression(
///     new DbFunctionCall("TO_NUMBER", new object[] { 
///           new DbFunctionCall("TO_CHAR ({0}, 'MM')", new object[] { PersonFields.DateOfBirth})  })  );
/// ---


/// Include in filter
FieldBetweenPredicate betweenPredicate = new FieldBetweenPredicate(expressionToFilter, null, 1, 4);         
IRelationPredicateBucket filter = new RelationPredicateBucket(betweenPredicate);

Here I use Between predicate, you could interchange it with whatever filter you need:

filter.PredicateExpression.Add(expressionToFilter == 1);

filter.PredicateExpression.Add(expressionToFilter >= 1  & expressionToFilter <=4);

int[] values = new int[3] {1, 2, 5};
filter.PredicateExpression.Add(expressionToFilter == values);

If something is wrong, please review the generated SQL (Ref: LLBLGenPro Help - Using generated code - Troubleshooting and debugging).

Hope helpful