When do you use Query Table?

[ThiPham12]

I am relatively new to ArcGIS Pro SDK for .NET and C#. I am working on creating an app that performs some statistical calculations using a few geodatabase tables in a single geodatabase. There are two tables with unique, common field in both tables. I want to grab the rows where the unique ID are present in both tables. I only want data from specific columns in each tables. I was wondering if the best way to do this is by creating a queryTable or using a list or dictionary? If you recommend using a queryTable, why and is there any resources you recommend on learning how to use a queryTable? I tried using the ESRI Prosnippets. However, it does not have the information I need. I added some samples of codes I tried using the queryTable and what I would like to perform. The indentation was not copied correctly below. 

namespace ToolName
{
public class ClassName
{

public List<string> Cokey { get; set; }

public All_Processes()
{
Cokey = new List<string>();

}

string gdbPath1 = "C:/...";
using (Geodatabase geodatabase = new Geodatabase(new FileGeodatabaseConnectionPath(new Uri(gdbPath1)))){

using(Table compTable = geodatabase.OpenDataset<Table>("Component")){
int cokey_count = 0;
using (RowCursor cursor = compTable.Search()){
while (cursor.MoveNext())
{
cokey_count++;
Cokey.Add(values);

//From this table, I want the cokey column, as well as the corresponding, Component_Rep column in the same table. 

}
}

}

//Creating the Horizon query table
using (Table HzTable = geodatabase.OpenDataset<Table>("Horizon"))
{
QueryDef queryDef = new QueryDef
{
Tables = "Horizon",
SubFields = "Horizon.hzdept_r, Horizon.hzdepb_r, Horizon.awc_l,Horizon.awc_r, Horizon.awc_h, Horizon.wfifteenbar_l,Horizon.wfifteenbar_r, Horizon.wfifteenbar_h, Horizon.wtenthbar_l,Horizon.wtenthbar_r, Horizon.wtenthbar_h, Horizon.wthirdbar_l,Horizon.wthirdbar_r, Horizon.wthirdbar_h, Horizon.wsatiated_l, Horizon.wsatiated_r, Horizon.wsatiated_h",

};

QueryTableDescription queryTableDescription = new QueryTableDescription(queryDef)
{
Name = "HzTable",
PrimaryKeys = geodatabase.GetSQLSyntax().QualifyColumnName("Horizon", "OBJECTID")
};

Table queryTable = geodatabase.OpenQueryTable(queryTableDescription);

// After creating the query table, I want to perform the following statistical calculations if the cokey  (unique ID) in both table present: 

If(cokey==Cokey){

for (var x = 0; x > nu_cokey; x++)
{

//Calculating the soil thickness, rThick by subtracting the bottom soil depth, botDepth (hzdepb_r column)  to the top soil depth, topDepth(Horizon.hzdept_r)
rThick = botDepth - topDepth;  

//Calculating the representative low by using the minimum value of the soil property within a specific column, awc_l
rLow = Table.CalculateStatistics.Min(rLow, rLow_Value);

//there are more equations below
}

}

[Wolf]

The concepts for QueryTable can be found here:

ProConcepts Geodatabase · ArcGIS/arcgis-pro-sdk Wiki (github.com) 

As to whether you should use a Dictionary object or open a QueryTable my general rule is to use a  dictionary for small datasets because the code is simple and maintenable, however, dictionaries don't work well when you are dealing with large datasets, in that case it will be more efficient to match-up your records on the service level and then only deal with the matched records.

In the following sample matches records from two feature classes using a QueryTable and then collects the result in a dictionary.  This is the output:

And the code snippet:

protected override async void OnClick()
{
  try
  {
	var layer1Name = "TestPoints";
	var layer2Name = "TestLines";
	var layer1JoinColumnName = "CodePoint";
	var layer2JoinColumnName = "Code";
	var result = await QueuedTask.Run(() =>
		  {
			var defGdb = new FileGeodatabaseConnectionPath(new Uri(Project.Current.DefaultGeodatabasePath));
			var dicResult = new Dictionary<long, List <(long, long)>>();
			using (Geodatabase geodatabase = new Geodatabase(defGdb))
			{
			  QueryDef queryDef = new QueryDef
			  {
				Tables = $@"{layer1Name} JOIN {layer2Name} on {layer1Name}.{layer1JoinColumnName} = {layer2Name}.{layer2JoinColumnName}",
				SubFields = $@"{layer1Name}.{layer1JoinColumnName}, {layer1Name}.TheInteger, {layer2Name}.TheInteger",
			  };

			  QueryTableDescription queryTableDescription = new QueryTableDescription(queryDef)
			  {
				Name = "JoinedPointLine",
				PrimaryKeys = geodatabase.GetSQLSyntax().QualifyColumnName(layer1Name, layer1JoinColumnName)
			  };

			  Table queryTable = geodatabase.OpenQueryTable(queryTableDescription);
			  using (RowCursor cursor = queryTable.Search())
			  {
				while (cursor.MoveNext())
				{
				  using (var row = cursor.Current)
				  {
					var key = Convert.ToInt64(row[0]);
					var lyr1Int = Convert.ToInt64(row[1]);
					var lyr2Int = Convert.ToInt64(row[2]);
					if (dicResult.ContainsKey(key))
						dicResult[key].Add ((lyr1Int, lyr2Int));
					else
						dicResult.Add(key, new List<(long, long)> { (lyr1Int, lyr2Int) });
				  }
				}
			  }
			}
			return dicResult;
		  });
	var sb = new StringBuilder();
	foreach (var key in result.Keys)
	{
	  sb.AppendLine($@" code: {key}");
	  foreach (var item in result[key])
	  {
		sb.AppendLine($@"  {item.Item1}, {item.Item2}");
	  }		  
	}
	MessageBox.Show($@"Results: {result.Keys.Count}{System.Environment.NewLine}{sb.ToString()}");
  }
  catch (Exception ex)
  {
	MessageBox.Show($@"Error: {ex}");
  }
}

[ThiPham12]

Hi Wolf, 

Thanks for your help. Line 38 and 40 looks very similar. What is the difference?

After reviewing the dataset closer, I found out that sometimes I need to join Table 1 to Table 2 and sometime vice-versa, depending on the user input. I was thinking I could create two query tables and add a query filter for the second table. How would I create the query filter for the second table using just the rows with the matching common fields? I was thinking something like this:

QueryFilter queryFilter = new QueryFilter() { WhereClause = "'cokey'='Cokey'" };

If you have a better recommendation, please let me know. Thanks Wolf!

[Wolf]

Line 38 and 40 deal add to an existing dictionary entry (if ContainsKey is true) or creates a new dictionary entry.  As for your second QueryTable simply swap layer1 and layer2 and the corresponding field names.

[ThiPham12]

Hi Wolf, 

Do you mean to use a If statement to determine the order of the tables joined together and create two joined tables? Thanks for your help!

[Wolf]

You asked in your post above:

". ..  join Table 1 to Table 2 and sometime vice-versa ..."

I think if you need to join Table 1 to Table 2 and then join Table 2 to Table 1 that would make it two QueryTables.  

[ThiPham12]

Hi Wolf, 

When I tried to create the Query Table, I keep getting an error that says the Query Table is not found. What are some common reason for this error? Thanks!

[Wolf]

I can't see your database nor your code, but I would suggest that you specified a table name that doesn't exist in your QueryTableDescription parameter.