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

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

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> GetUnique(IFeatureClass fc, string fldName)
{
    var outDict = new Dictionary>();
    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 ? "" : feat.get_Value(idx).ToString();
            if (!outDict.ContainsKey(key))

                outDict.Add(key, new List());
            outDict[key].Add(feat.OID);
        }
    }
    catch
    {
        throw;
    }
    finally
    {

        if(fCur != null)
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(fCur);
    }
    return outDict;
}