Hi,

I have a situation in which I need to apply a _tocompstring _function on all elements in the IN section.

ex SQL PSEUDO CODE:

select .....
from .....
WHERE x = 12
and y IN(
tocompstring ('BraVo'),
tocompstring ('MikE'),
tocompstring  ('Yankee'),
......
)

I achieved a result using the following predicate:

filter.Add(A.X == someId);

            IPredicateExpression tFilter = new PredicateExpression();

            foreach (string c in col)
            {
                tFilter.AddWithOr(A.Y.SetExpression(new DbFunctionCall("someschema.tocompstring({0})", new object[] { A.Y})) ==
                                       new DbFunctionCall("someschema.tocompstring({0})", new object[] { c}));
            }

            filter.AddWithAnd(tFilter);

which gaves me:

select .....
from .....
WHERE x = 12
and (y = tocompstring ('BraVo')
or y = tocompstring ('MikE')
or y = tocompstring ('Yankee')
......
)

How can I convert it into a query with 'IN' clausule?

Best Regards, MiloszeS

What does "tocompstring" do?

I'm asking this becaue for most db functions you can find an inverse function (which does the opposite), so you can do something like:

select .....
from .....
WHERE x = 12
and InverseFunction(y) IN('BraVo', 'MikE', 'Yankee',
......
)

The "tocompstring" function is function provided by my client which removes non comparable chars as well as changing many chars to comparable version ex. deutch 'B' to 'ss', etc.

I just found out, that I need to complicate a little bit my expression because values calculated based on that fields are stored only in the index, not in a table fields.

So, i need to make the following query:

...
tocompstring(y) in (
tocompstring(...),
tocompstring(...),
tocompstring(...),
.....
)

You can try to derive from FieldCompareRangePredicate class. And override the ToQueryText(), and there call the base method first then modify the returned string as you want. you can parse the string and foreach IN parameter replace it with the tocompstring(...).

Use the derived class in the PredicateExpression.