I need to return all rows from an Oracle table that begin with "VTX_".

The underscore is an Oracle Wildcard, and I need to run the following query against Oracle to get the proper rows:

select * from psroledefn where rolename like 'VTX_%' ESCAPE '\'

Any predicate like " 'VTX_%" gives me rows that have VTX in the first 3 characters, which is not what I want. I can't seem to come up with the correct FieldLikePredicate to escape that underscore.

I am using GenPro adapter with v. 3.5, MS Oracle Driver, GenPro Runtime FW.

Hi there. I would create a custom FieldLikePredicate that produces the ESCAPE clause, it will inherit from the normal FieldLikePredicate and we will override the ToQueryText method. Here is how it would look like:

using SD.LLBLGen.Pro.ORMSupportClasses;

namespace MyCompany.CustomPredicates
{
    public class FieldLikePredicateEscaped : FieldLikePredicate
    {
        #region SelfServicing Constructors
        /// <summary>
        /// CTor for Field LIKE Pattern. 
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        public FieldLikePredicateEscaped(IEntityField field, string pattern) : base(field, pattern)
        {
        }


        /// <summary>
        /// CTor for Field LIKE Pattern. 
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="objectAlias">Alias for the object the field belongs to. Used to identify which entity to use when the entity
        /// is present multiple times in a relation collection. Alias has to match an alias specified in the relation collection or should be
        /// left empty if no alias is specified (or no relation collection is used). In that case, use another overload.</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        public FieldLikePredicateEscaped(IEntityField field, string objectAlias, string pattern):base(field, objectAlias, pattern)
        {
        }


        /// <summary>
        /// CTor for Field LIKE Pattern. 
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        /// <param name="negate">Flag to make this expression add NOT to itself</param>
        public FieldLikePredicateEscaped(IEntityField field, string pattern, bool negate):base(field, pattern, negate)
        {
        }

        
        /// <summary>
        /// CTor for Field LIKE Pattern. 
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="objectAlias">Alias for the object the field belongs to. Used to identify which entity to use when the entity
        /// is present multiple times in a relation collection. Alias has to match an alias specified in the relation collection or should be
        /// left empty if no alias is specified (or no relation collection is used). In that case, use another overload.</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        /// <param name="negate">Flag to make this expression add NOT to itself</param>
        public FieldLikePredicateEscaped(IEntityField field, string objectAlias, string pattern, bool negate):base(field, objectAlias, pattern, negate)
        {
        }
        #endregion

        #region Adapter Constructors
        /// <summary>
        /// CTor for Field LIKE Pattern. 
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="persistenceInfo">The persistence info object for the field</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        public FieldLikePredicateEscaped(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, string pattern):base(field, persistenceInfo, pattern)
        {
        }


        /// <summary>
        /// CTor for Field LIKE Pattern. 
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="persistenceInfo">The persistence info object for the field</param>
        /// <param name="objectAlias">Alias for the object the field belongs to. Used to identify which entity to use when the entity
        /// is present multiple times in a relation collection. Alias has to match an alias specified in the relation collection or should be
        /// left empty if no alias is specified (or no relation collection is used). In that case, use another overload.</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        public FieldLikePredicateEscaped(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, string objectAlias, string pattern):base(field, persistenceInfo, objectAlias, pattern)
        {
        }

        
        /// <summary>
        /// CTor for Field LIKE Pattern. 
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="persistenceInfo">The persistence info object for the field</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        /// <param name="negate">Flag to make this expression add NOT to itself</param>
        public FieldLikePredicateEscaped(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, string pattern, bool negate):base(field, persistenceInfo, pattern, negate)
        {
        }

        
        /// <summary>
        /// CTor for Field LIKE Pattern. 
        /// </summary>
        /// <param name="field">Field to compare with the LIKE operator</param>
        /// <param name="persistenceInfo">The persistence info object for the field</param>
        /// <param name="objectAlias">Alias for the object the field belongs to. Used to identify which entity to use when the entity
        /// is present multiple times in a relation collection. Alias has to match an alias specified in the relation collection or should be
        /// left empty if no alias is specified (or no relation collection is used). In that case, use another overload.</param>
        /// <param name="pattern">Pattern to use in the LIKE expression</param>
        /// <param name="negate">Flag to make this expression add NOT to itself</param>
        public FieldLikePredicateEscaped(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, string objectAlias, string pattern, bool negate)
            :base(field, persistenceInfo, objectAlias, pattern, negate)
        {
        }
        #endregion

        /// <summary>
        /// Retrieves a ready to use text representation of the contained Predicate.
        /// </summary>
        /// <param name="inHavingClause">if set to true, it will allow aggregate functions to be applied to fields.</param>
        /// <returns>
        /// The contained Predicate in textual format.
        /// </returns>
        /// <exception cref="System.ApplicationException">When IPredicate.DatabaseSpecificCreator is not set to a valid value.</exception>
        public override string ToQueryText(bool inHavingClause)
        {
            return string.Format(@"{0} ESCAPE '\'", base.ToQueryText(inHavingClause));
        }
    }
}

Put that class in your own project (gui, business layer, etc).

And the usage:

// adapter
var filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(
                new FieldLikePredicateEscaped(DepartmentFields.DepartmentName, null, @"Hack\_%"));

// selfservicing
var filter = new PredicateExpression();
filter.Add(
     new FieldLikePredicateEscaped(DepartmentFields.DepartmentName, @"Hack\_%"));