Im using a situation where my table data contains 'like' comparison operators/wildcards.... (ex... '%this','%that%','other%')

since MSSQL only allows comparison wildcards on the right side of a comparison...

table.column like '%this%'

it means that a query like the following will not return any rows...

A): where table.column like 'this is a string'

HOWEVER, the following does work...

B): where 'this is a string' like table.column

it would return the row where the value of '%this' was in the column

SO, the question is... a predicate of...

RelationPredicateBucket bucket = new RelationPredicateBucket(TableFields.MyColumn % strMatch);

will create example A above...

How can I get LLBL to create example B (2.6, Adapter, SQL 2005)?

I looked at creating a custom IExpression but that only seemed to allow me to adjust the right side of the comparison.

Im looking at a custom IPredicate now... right direction?

Okay, I found the FieldLikePredicate source code down in the install folder and just made my own FieldReverseLikePredicate that I can include in my projects.

I don't know how useful it is but it would be easy to add an overload to the FieldLikePredicate Ctor that took a 'reverse' flag that would allow this behavior out of the box in 2.7 :-)

Thanks for Listening while I talk to myself.

I think this is a very edge case, so in those situations, people should grab the sourcecode and alter the predicate class to create their own.

yep, agreed. Once again LLBL saves the day!!

Thanks Otis!!!

Alright... here is the final product....

using System; using System.Text; using System.Data;

using SD.LLBLGen.Pro.ORMSupportClasses;

namespace TH.Core.Data.LLBL { /// <summary> /// Reverse Implementation of a LIKE predicate expression , /// using the following formats: /// IEntityField(Core) LIKE Parameter (f.e. Foo LIKE @Foo ) /// A specified pattern will be set as the parameters value. /// </summary> [Serializable] public class FieldReverseLikePredicate : FieldLikePredicate {

    /// <summary>
    /// CTor
    /// </summary>
    public FieldReverseLikePredicate() : base()
    {
    }


    /// <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 FieldReverseLikePredicate(IEntityField field, string pattern)
        : base(field, field, pattern, false)
    {
    }


    /// <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 FieldReverseLikePredicate(IEntityField field, string pattern, bool negate)
        : base(field, field, 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="pattern">Pattern to use in the LIKE expression</param>
    public FieldReverseLikePredicate(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, string pattern)
        : base(field, persistenceInfo, pattern, false)
    {
    }


    /// <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 FieldReverseLikePredicate(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, string pattern, bool negate)
        : base(field, persistenceInfo, pattern, negate)
    {
    }

    /// <summary>
    /// Implements the IPredicate ToQueryText method. Retrieves a ready to use text representation of the contained Predicate.
    /// </summary>
    /// <param name="uniqueMarker">int counter which is appended to every parameter. The refcounter is increased by every parameter creation,
    /// making sure the parameter is unique in the predicate and also in the predicate expression(s) containing the predicate.</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(ref int uniqueMarker)
    {
        return ToQueryText(ref uniqueMarker, false);
    }

    /// <summary>
    /// Retrieves a ready to use text representation of the contained Predicate. 
    /// </summary>
    /// <param name="uniqueMarker">int counter which is appended to every parameter. The refcounter is increased by every parameter creation,
    /// making sure the parameter is unique in the predicate and also in the predicate expression(s) containing the predicate.</param>
    /// <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(ref int uniqueMarker, bool inHavingClause)
    {
        if (base.FieldCore == null)
        {
            return "";
        }

        if (base.DatabaseSpecificCreator == null)
        {
            throw new System.ApplicationException("DatabaseSpecificCreator object not set. Cannot create query part.");
        }

        this.Parameters.Clear();

        StringBuilder queryText = new StringBuilder(64);

        if (base.Negate)
        {
            queryText.Append("NOT ");
        }

        // create parameter 
        uniqueMarker++;
        IDataParameter parameter = this.DatabaseSpecificCreator.CreateLikeParameter(String.Format("{0}{1}", base.FieldCore.Name, uniqueMarker), base.Pattern,
                base.PersistenceInfo.SourceColumnDbType);

        string fieldName = this.DatabaseSpecificCreator.CreateFieldName(base.FieldCore, base.PersistenceInfo, base.FieldCore.Name, this.ObjectAlias, ref uniqueMarker, inHavingClause);
        if (base.CaseSensitiveCollation)
        {
            queryText.AppendFormat(null, "{2} LIKE {0}({1})", this.DatabaseSpecificCreator.ToUpperFunctionName(),
                fieldName, parameter.ParameterName);
        }
        else
        {
            queryText.AppendFormat(null, "{1} LIKE {0}", fieldName, parameter.ParameterName);
        }

        // first the field's expression parameter...
        if (base.FieldCore.ExpressionToApply != null)
        {
            for (int i = 0; i < base.FieldCore.ExpressionToApply.Parameters.Count; i++)
            {
                this.Parameters.Add(base.FieldCore.ExpressionToApply.Parameters[i]);
            }
        }

        // then the pattern's parameter.
        this.Parameters.Add(parameter);
        return queryText.ToString();
    }

}

}

Thanks for the contribution

This will be handy for me as well. Thanks for the post.