I am trying to create my own SQL Server where predicate.

I want to implement the clause WHERE Difference(FieldX, Value) > 3.

I see a similar thread here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=2742&HighLight=1

I have used the source for the FieldCompareValuePredicate class to create my own class, changing the ToQueryText method to emit my SQL Code.

I am receiving an error:

'SD.LLBLGen.Pro.ORMSupportClasses.Predicate' does not contain a definition for 'SelfServicing'

This is happening on the initialization routine:

 base.SelfServicing = selfServicing;

How should I get around this? Why doesn't the base class contain this property? I am using adapter templates.

Is there a better way to make custom predicates?

Thanks,

Can1

that's an internal property.

But you don't need that. What you need is an implementation of IExpression, then set the field's ExpressionToUse property to an instance of that implementation. See for hints here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

This is an example of a function with 1 parameter, but you can of course add a property which accepts the value to pass as well to Difference(). If you need further help, please post your code so far so we can correct that if necessary

Thanks Frans, that worked great.

One last question on the parsing of the ToQueryText value. When I use the following code to implement the ToQueryText method:

return string.Format("DIFFERENCE({1},'{0}')", _value, base.ToQueryText(ref uniqueMarker, inHavingClause));
        }

, it emits the following SQL:

WHERE ( DIFFERENCE([IHHP360].[dbo].[tblUser].[ftxtLastName],''Part'') >= @LastName1)

The single apostrophe's are getting doubled up. If I remove the single apostrophe's from the ToQueryText method:

return string.Format("DIFFERENCE({1},{0})", _value, base.ToQueryText(ref uniqueMarker, inHavingClause));
        }

it does not encapulate the string literal in single apostrophe's at all and of course, SQL Server reports a syntax error.

Any thoughts?

Thanks,

Can1

Try to create a parameter, and use that instead. Use the creator object's CreateParameter(name, direction, value) to do that

Having trouble getting the parameter working. I have found a few examples in the forums but can't get it quite right. My Code:

public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
    {
        if (_value.Length > 0)
        {
            IDataParameter parameter = base.DatabaseSpecificCreator.CreateParameter("SoundexParam", ParameterDirection.Input, _value);
            base.Parameters.Add(parameter);
            uniqueMarker++;
            parameter.ParameterName += uniqueMarker.ToString();

            return string.Format("DIFFERENCE({1},{0})", parameter.ParameterName, base.ToQueryText(ref uniqueMarker, inHavingClause));
        }
        else
        {
            return base.ToQueryText(ref uniqueMarker, inHavingClause);
        }
    }

Results in:

WHERE ( DIFFERENCE([IHHP360].[dbo].[tblUser].[ftxtLastName],@SoundexParam2) >= @LastName1)',N'@LastName1 int',@LastName1=3

The Parameter is getting put into the string, but the definition of the parameter is not getting added to the overall statement definition. I thought the base.Parameters.Add(parameter) would add it to the base statement when the expression is applied to the .FieldCore.ExpressionToApply property?

Can1

the base.ToQueryText() clears the parameters collection. Place base.Parameters.Add(parameter);

after the base.ToQueryText().

That worked great!

I am partly through my first project with LLBLGen and I must say, quite a product. I am very impressed. It really saves a lot of time, and more importantly I think, allows for more consistent better quality code in the end. On top of that, your support is equal to none. Thank-you Frans. You are definetely going to have a long-time customer here.

can1 wrote:

That worked great!

I am partly through my first project with LLBLGen and I must say, quite a product. I am very impressed. It really saves a lot of time, and more importantly I think, allows for more consistent better quality code in the end. On top of that, your support is equal to none. Thank-you Frans. You are definetely going to have a long-time customer here.

Thank you! .