C# QueryFilter prefixClause "DISTINCT" non-functional

[MikeRatcliffe]

I'm finding that QueryFilter prefixClause "DISTINCT" is non-functional in the SDK and must use a workaround:

                QueryFilter filter = new QueryFilter
                {
                    //PrefixClause = "DISTINCT",              //DISTINCT does not work in this query. See workaround below.
                    SubFields = "FY",
                    WhereClause = "FY IS NOT NULL AND FY <> '-9' AND FY <> ' '",
                    PostfixClause = "ORDER BY FY"
                };

                using (RowCursor rowCursor = sso_lyr.Search(filter))
                {
                    int fldIndex = rowCursor.FindField("FY");

                    List<string> valuelist = new List<string>();

                    while (rowCursor.MoveNext())
                    {
                        string value = rowCursor.Current.GetOriginalValue(fldIndex) as string;
                        valuelist.Add(value);
                    }

                    IEnumerable<string> distinctvaluelist = valuelist.Select(x => x).Distinct(); //distinct from list b/c DISTINCT prefixclause non-functional above.

                    foreach (string item in distinctvaluelist)
                    {
                        Invoke(new MethodInvoker(delegate { Sso_combo.Items.Add(item); }));
                    }
                }
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This is okay for very short lists of values, but not for 100,000 records; which is the requirement.

Has anyone else encountered this?  I'm using documentation from this source:

https://github.com/esri/arcgis-pro-sdk/wiki/ProConcepts-Geodatabase

Word find: "distinct" on the page...

[RichRuh]

Hi Mike,

What kind of database are you trying to run this against?

Thanks,

--Rich

[MikeRatcliffe]

Hey, Rich.

it is a FeatureLayer with the source of SSO_Events.shp > SSO_Events.lyr.

[RichRuh · Accepted solution]

Shape files do not support the Distinct clause.  

To see what clauses are supported by a particular datastore, you can call Datastore.GetSQLSyntax().  This method returns a SQLSyntax object.  SQLSyntax.GetSupportedClauses() returns a list of the supported clauses.

I'll update the conceptual doc to make this more clear.

--Rich

[MikeRatcliffe]

Thanks, Rich.