I am attempting to use the FieldCompareSetPredicate with SetOperator.In to see if a I have any matches based on a list of ID's I am handing in. That list is myString. arr is simply an ArrayList that I'm scraping for each value.

string myString = string.Empty;
    for(int i = 0;i<arr.Count;i++)
    {
        myString += arr[i].ToString();
        if(i!=arr.Count -1)
            myString += ",";
    }
bucket.PredicateExpression.Add(new FieldCompareSetPredicate(
    EntityFieldFactory.Create(PromotionFieldIndex.PromotionID), null,
    EntityFieldFactory.Create(Group_PromotionFieldIndex.PromotionID),null,
    SetOperator.In,
    PredicateFactory.CompareValue(Group_PromotionFieldIndex.GroupID,ComparisonOperator.Equal,myString)));

It's not until I try to fetch the collection based on the above that an error is thrown:

An exception was caught during the execution of a retrieval query: Input string was not in a correct format.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

What is the correct method to handing it a list of known ID's to emulate the in () functionality is sql?

Also, which exception should I catch so I can actually view the QueryExecuted and Parameters? I've tried the ones in the documentation but something goes awry there...

You should use FieldCompareRangePredicate. That predicate's constructor (or if you use the PredicateFactory, the CompareRange method) accepts an array or arraylist of values. So split the values on ',' and pass on the array to the predicate constructor / predicatefactory.CompareRange method.

FieldCompareSetPredicate is for sets which are formed in the database.

Ok, then I think I'm missing something here. I see that the FieldCompareRangePredicate is meant to handle multiple values. I'm not sure how I overlooked that.

bucket.PredicateExpression.AddWithOr(
    new FieldCompareRangePredicate(
        EntityFieldFactory.Create(Group_PromotionFieldIndex.GroupID),
        null, arr));

What I don't get is this:

The in () expression is looking for all elements from table A in it's related table B. Promotion being A and Group_Promotion being B. I understand how to use it if I wanted all Promotions where PromotionID in (1,2,3) but what I'm looking to do is:

Select p.*
From Promotion p
JOIN Group_Promotion gp ON gp.PromotionID in (1,2,3)

Does that make sense?

Edited to change the PredicateExpress.Add to PredicateExpression.AddWithOr. This bucket is doing something similiar for other related entities as well and then I do one big FetchCollection for the whole mess. If I leave out this one problem area, the others all work together as expected. The Group_Promotion relation is a bit of a stickler as it's the only one that requires the in () expression.

It would look something like this (Adapter):

bucket.PredicateExpression.AddWithOr(new FieldCompareSetPredicate(
                        EntityFieldFactory.Create(PromotionFieldIndex.PromotionID), null,
                        EntityFieldFactory.Create(Group_PromotionFieldIndex.PromotionID),null,
                        SetOperator.In,
                        new FieldCompareRangePredicate(
                        EntityFieldFactory.Create(Group_PromotionFieldIndex.GroupID),
                        null, arr)));

Seems to work.

curt_h wrote:

Ok, then I think I'm missing something here. I see that the FieldCompareRangePredicate is meant to handle multiple values. I'm not sure how I overlooked that.

bucket.PredicateExpression.AddWithOr(
    new FieldCompareRangePredicate(
        EntityFieldFactory.Create(Group_PromotionFieldIndex.GroupID),
        null, arr));

What I don't get is this:

The in () expression is looking for all elements from table A in it's related table B. Promotion being A and Group_Promotion being B. I understand how to use it if I wanted all Promotions where PromotionID in (1,2,3) but what I'm looking to do is:

Select p.*
From Promotion p
JOIN Group_Promotion gp ON gp.PromotionID in (1,2,3)

Does that make sense?

That query is the same as:

Select p.*
From Promotion p
INNER JOIN Group_Promotion gp ON p.PromotionID = gp.PromotionID 
where gp.PromotionID in (1,2,3)

or

Select p.*
From Promotion p
Where p.PromotionID IN
(select PromotionID from Group_Promotion gp.PromotionID in (1,2,3))

Your query which works does the latter.