ArcObjects: Alternative workflow to using a SQL query with over 1000 values in IN statement

Question

I have a ListBox listing the unique values of a column in a feature class; the user can select any number of values and click a button to then zoom to the set of features matching those values. A function builds a WHERE clause that packs the selected values into an IN statement.

This works great when the number of selected values is <= 1000, but if there are over 1000 then no rows are returned. This appears to be an Oracle limitation (the issue does not occur on shapefiles or file geodatabase feature classes), for which some Oracle-specific workarounds are described in this StackOverflow question, but I want to make this function database-agnostic so that it works with shapefiles and file geodatabases as well as SDE geodatabases.

Can anyone suggest any alternative workflows that are 1) implemented in ArcObjects; and 2) database-agnostic; to using an IN statement with 1000+ values? I had thought about creating an in-memory table consisting of the selected values and joining against it but have not attempted this yet, pending any better ideas that may pop up here! If you know of the specific interfaces I should be looking at, that would be very helpful as well.

Answer 1

Depending on performance requirements, you might consider loading a dictionary of objectid lists keyed by unique attribute at startup. Populate your picklist from the dictionary keys, then when the user picks from the list you would call IGeodatabaseBridge2.GetFeatures(), which has no limit AFAIK on the size of the objectID array passed to it.

Here's the code to load the dictionary:

public Dictionary<string, List<int>> GetUnique(IFeatureClass fc, string fldName)
{
    var outDict = new Dictionary<string, List<int>>();
    int idx = fc.Fields.FindField(fldName);
    if (idx == -1)
        throw new Exception(String.Format("field {0} not found on {1}", fldName, ((IDataset)fc).Name));

    IFeatureCursor fCur = null;
    IFeature feat;
    try
    {
        var qf = new QueryFilterClass();
        qf.SubFields = fc.OIDFieldName + "," + fldName; // updated per comment
        fCur = fc.Search(qf, true);
        while ((feat = fCur.NextFeature()) != null)
        {
            string key = feat.get_Value(idx) is DBNull ? "<Null>" : feat.get_Value(idx).ToString();
            if (!outDict.ContainsKey(key))
                outDict.Add(key, new List<int>());
            outDict[key].Add(feat.OID);
        }
    }
    catch
    {
        throw;
    }
    finally
    {
        if(fCur != null)
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(fCur);
    }
    return outDict;
}

Answer 2

Breaking down the set into multiple sets at the SQL level is, in my opinion, a pragmatic solution, as well as far easier and more performant than any other approach involving any kind of intermediate in-memory storage.

If you break it down as follows:

COL in (1, 2, 3, ..., 1000) OR 
COL in (1001, 1002, 1003, ...,2000)

you will meet your requirement of database indifference since this will work across all the database engines ArcObjects are capable of working with. Introducing such logic will of course mean there is some kind of inherent implementation-level knowledge in your code, but it's a small price to pay compared to other solutions.