topic Re: Getting Distinct Values - ArcGIS .NET SDK in .NET Maps SDK Questions

Getting Distinct Values - ArcGIS .NET SDK

BradleyAndrick3 — Mon, 27 Aug 2018 18:24:11 GMT

We have a workflow where we are building a search control to allow users to search through data in a custom application written with the ArcGIS .NET SDK. This is an offline application that uses mobile geodatabases (.geodatabase) to store and sync back when in a service area. We are allowing the user to select 3 things:

1) a layer name from a dropdown (this is easy to query the map for layers and add the names to the dropdown list)

2) a list of fields from a dropdown (this is also easy as we can add this from the field list of each map layer and alter this list when the user selects a different layer name from the first dropdown)

3) this is our problem: we would like to have a third dropdown that is populated with a list of distinct values of the field that is selected.

We need to be able to have something to the equivalent of SELECT DISTINCT, however, if we attempt to query the feature table (QueryFeaturesAsync) we can only add to the property of the where statement, which does not allow for the DISTINCT prefix to be added to our query parameters.

Additionally, we tried to sort through the result that is returned for all results using "1=1" which returns a FeatureQueryResult but this takes a very long time to return - we have hundreds of thousands of records potentially being returned which we would then have to iterate through each to get the values and add them to the list if they do not already exist in the list. This is very poor programming methodology and is way to slow to be used in production.

We noticed that the REST Services API has a query parameter of returnDistinctValues. This is essentially what we need to use in the .NET sdk but it does not seem to exist. The ideal workflow would be to set a query parameter for the outFields to match our second dropdown value and then use the returnDistinctValues property set to 'true' to populate the third dropdown from the distinct results.

This seems like a very basic task that we are able to run in the Enterprise GDB DBMS very easily and the returned list is very fast to complete.

Looking for some assistance on this. Is it possible? Are we just missing something?

Re: Getting Distinct Values - ArcGIS .NET SDK

JoeHershman — Sat, 11 Dec 2021 12:37:45 GMT

Not sure if the Runtime team will like this approach, but the .geodatabase is just a SqLite database. If you just need to get the values to populate a list you could use the System.Data.SQLite API. I'm sure it will be a LOT faster than trying to query the table and find the values. Something along these lines

string connectioString = $"Data Source={filename}";

using (SQLiteConnection connection = new SQLiteConnection(connectioString))
{
     connection.Open();

     string query = "Select Distinct(fieldname)from thetable";

     SQLiteCommand command = new SQLiteCommand(query, connection);

     var reader = command.ExecuteReader();

     while ( reader.Read() )
     {
        uniqueValuesList.Add(reader[fieldname].ToString()});
     }
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

We do some direct queries of the table to get information about the replica in the GDB_Items table. We do these queries before the table is open in the map so I am not sure if there would be an issue if you were doing when the .geodatabases where loaded into the map

Re: Getting Distinct Values - ArcGIS .NET SDK

BradleyAndrick3 — Wed, 29 Aug 2018 19:49:11 GMT

Hi Joe,

Great input, I left this out of my question but that is actually what we are already using for our work-around. You are correct that it is super fast and very efficient with system resources. It also doesn't appear to put any locks on the data even with the data added to the map which is nice and works well for us. We just wanted to know if there was an "esri approved methodology" out there. I'm going to guess not.

Thanks for the answer.