Hi,

I am trying to construct a where clause that is as follows;

SELECT GroupName FROM Group INNER JOIN .... (this part doesn't matter)

WHERE fn_CountGroupsInBrochure(GroupId, 'BrochureName') > 0

I can create an expression as follows

IExpression FunctionExpression = new Expression(new DbFunctionCall("fn_CountGroupsInBrochure", new object[] { GroupFields.GroupId, BrochureCode }), ExOp.GreaterThan, 0);

But I cannot see how to turn this into a predicate without involving a field name. I was hoping for

IPredicateExpression BrochureFilter = new PredicateExpression();
BrochureFilter.Add(FunctionExpression);

or something similar.

Help!!!

Hi Darren. LLBLGenPro Version? TemplateSet? Runtime Libraries? Look at this http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=9720, here is a example about including a DBFunctionCall in a PredicateExpression (Adapter, DynamicList).

Sorry, forgot to add that vital info!

V2, Self Serving, latest download.

I took a look at that thread but it uses a field in the predicate which I can't.

As I said above the WHERE statement is just checking a function return. Is there a way to use an expression in a predicate without involving a field?

Darren

You always need a IEntityField2 as an Expression not guarantee is a valid predicateExpression. However you can do somethink like:

// expression to apply for DBFunctionCall
IExpression functionExpression = new DbFunctionCall("YEAR", new object[] { OrdersFields.OrderDate });
IEntityField2 functionField = OrdersFields.OrderId.SetExpression(functionExpression);

// filter on dbfuntionCall field
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(
    new FieldCompareValuePredicate(functionField, null, ComparisonOperator.GreaterThan, 1995));
    

Thanks for that David, it put me on the right track. For anyone else in a similar predicament (or should that be a predicatement?), I did the following;

IExpression FunctionExpression = new DbFunctionCall("fn_CountGroupsInBrochure", new object[] { GroupFields.GroupId, BrochureCode });
EntityField TempField = new EntityField("Test", FunctionExpression);
IPredicateExpression BrochureFilter = new PredicateExpression();
BrochureFilter.Add(TempField > 0);