using low level api I subclassed FieldCompareRangePredicate to create a custom predicate in order to query with a very long in list by 1) inserting the items in the in list into a temp table then 2) creating a subquery within the overridden FieldCompareRangePredicate’s ToQueryText() to fetch from the temptable as shown below:

public class StringFieldCompareRangePredicateByTempTable : FieldCompareRangePredicate
{
    public override string ToQueryText(bool inHavingClause)
    {
        InsertIntoTempTable(Values);

        if (this.FieldCore == null)
        {
            return string.Empty;
        }

        if (Values.Count <= 0)
        {
            return this.Negate ? "1=1" : "1=0";
        }

        if (this.DatabaseSpecificCreator == null)
        {
            throw new ApplicationException("DatabaseSpecificCreator is not set.");
        }

        Parameters.Clear();

        var stringBuilder = new StringBuilder(127) ;
        stringBuilder.Append(DatabaseSpecificCreator.CreateFieldName(this.FieldCore, this.PersistenceInfo, FieldCore.Name, this.ObjectAlias, inHavingClause));
        stringBuilder.Append(" ");

        if (FieldCore.ExpressionToApply != null)
        {
            foreach (var parameter in FieldCore.ExpressionToApply.Parameters)
            {
                Parameters.Add(parameter);
            }
        }

        if (this.Negate)
        {
            stringBuilder.Append("NOT ");
        }

        stringBuilder.Append("IN (");
        stringBuilder.Append("select StringField from " + fullTempTableName);
        stringBuilder.Append(")");

        return stringBuilder.ToString();
    }
….
}

This worked fine.

Now I ‘must’ do something similar using QuerySpec (i.e., to be able to make queries with very long in list using QuerySpec). Querying using QuerySpec using in list is simple

var qf = new QueryFactory();
var q = qf.Customer
    .Where(CustomerFields.Id.In(IdList));

I am looking for way to replace the argument IdList above with my own sub query that accesses a temp table (I am aware that subquery accessing a regular table is supported).

Thank you for your help.

jp

QuerySpec.Where() accepts an IPredicate. Did you try using your FieldCompareRangePredicate subclass with it?

Wow, it works like a charm, thanks a lot.

Can I ask if the same IPredicate can be used in a Linq query, like in the following

var customers = from eh in metaData.Customer where IdList.Contains(eh.Id) select eh;

jp

That's only available in QuerySpec, you can't use PredicateExpression with Linq.