topic Re: Evaluate a QueryDef with joined tables or featureClass from a Feature Layer URL in ArcGIS Pro SDK Questions

Evaluate a QueryDef with joined tables or featureClass from a Feature Layer URL

RejeanLabbe — Mon, 18 Dec 2023 20:06:11 GMT

I have an issue when I use the Evaluate method with a QueryDef with joined tables or featureClass from a Feature Layer.

Everything works great when I use this code with a GDB or an SDE connection, but it doesn't work with a Feature Layer from ArcGIS Enterprise.

https://myserver.com/server/rest/services/myweblayer/FeatureServer

The connection to this Feature Layer works when I use a simple QueryFilter.

Here is a simplified version of my code :

using (Table oTable1 = oGeodatabase.OpenDataset<Table>(oTableDefinition.GetName())) using (FeatureClass oFeatureClass = oGeodatabase.OpenDataset<FeatureClass>(oFeatureClassDefinition.GetName())) { sTables = string.Format("{0} LEFT JOIN {1} ON {1}.MY_KEY = {0}.MY_KEY", oTable1.GetName(), oFeatureClass2.GetName()); oQueryDef = new QueryDef { Tables = sTables, SubFields = sSubFields, WhereClause = sWhere, }; using (RowCursor oRowCursor = oGeodatabase.Evaluate(oQueryDef, false)) { } }

My Feature Layer is on ArcGIS Enterprise but it might be the same for ArcGIS Online.

Re: Evaluate a QueryDef with joined tables or featureClass from a Feature Layer URL

Aashis — Mon, 18 Dec 2023 21:19:30 GMT

QueryDef is not supported in a feature service datastore.

Re: Evaluate a QueryDef with joined tables or featureClass from a Feature Layer URL

RejeanLabbe — Tue, 19 Dec 2023 19:31:54 GMT

Thanks @Aashis.

Amr from Esri Canada told me about the Join class from the ArcGIS.Core.Data namespace and that did the trick.

using (Geodatabase sourceGeodatabase = new Geodatabase(new FileGeodatabaseConnectionPath(new Uri("Path \\ to \\Geodatabase \\ one")))) using (Geodatabase destinationGeodatabase = new Geodatabase(new FileGeodatabaseConnectionPath(new Uri("Path \\ to \\Geodatabase \\ two")))) using (Table sourceTable = sourceGeodatabase.OpenDataset<Table>("State")) using (Table destinationTable = destinationGeodatabase.OpenDataset<Table>("Cities")) { Field primaryKeyField = sourceTable.GetDefinition().GetFields().FirstOrDefault(field => field.Name.Equals("State.State_Abbreviation")); Field foreignKeyField = destinationTable.GetDefinition().GetFields().FirstOrDefault(field => field.Name.Equals("Cities.State")); VirtualRelationshipClassDescription virtualRelationshipClassDescription = new VirtualRelationshipClassDescription(primaryKeyField, foreignKeyField, RelationshipCardinality.OneToMany); using (RelationshipClass relationshipClass = sourceTable.RelateTo(destinationTable, virtualRelationshipClassDescription)) { JoinDescription joinDescription = new JoinDescription(relationshipClass) { JoinDirection = JoinDirection.Forward, JoinType = JoinType.InnerJoin, TargetFields = sourceTable.GetDefinition().GetFields() }; using (Join join = new Join(joinDescription)) { Table joinedTable = join.GetJoinedTable(); //Process the joined table. For example .. using (RowCursor cursor = joinedTable.Search()) { while (cursor.MoveNext()) { using (Row row = cursor.Current) { // Use Row } } } } } }

Re: Evaluate a QueryDef with joined tables or featureClass from a Feature Layer URL

PARTHASARATHIU — Sun, 08 Dec 2024 18:41:30 GMT

QueryDef mainQueryDef = new QueryDef
{
Tables = $"DD_PLAN_1_STUDENT_COUNT LEFT JOIN ({"SELECT DD_PLAN_1_STA_ENROLLMENT.SCHL_ATND AS SCHOOL, Sum( DD_PLAN_1_STA_ENROLLMENT.TOTAL_ENROLLMENT) AS TOTALENROLL, SUM(DD_PLAN_1_STA_ENROLLMENT.GPK+DD_PLAN_1_STA_ENROLLMENT.GK+DD_PLAN_1_STA_ENROLLMENT.G1+" +
"DD_PLAN_1_STA_ENROLLMENT.G2+DD_PLAN_1_STA_ENROLLMENT.G3+DD_PLAN_1_STA_ENROLLMENT.G4+DD_PLAN_1_STA_ENROLLMENT.G5+DD_PLAN_1_STA_ENROLLMENT.G6) AS [PK-6], SUM(DD_PLAN_1_STA_ENROLLMENT.GPK) AS GPK, SUM(DD_PLAN_1_STA_ENROLLMENT.GK) AS GK, SUM(DD_PLAN_1_STA_ENROLLMENT.G1) AS G1, SUM(DD_PLAN_1_STA_ENROLLMENT.G2) AS G2, " +
"SUM(DD_PLAN_1_STA_ENROLLMENT.G3) AS G3, SUM(DD_PLAN_1_STA_ENROLLMENT.G4) AS G4, SUM(DD_PLAN_1_STA_ENROLLMENT.G5) AS G5, SUM(DD_PLAN_1_STA_ENROLLMENT.G6) AS G6, SUM(DD_PLAN_1_STA_ENROLLMENT.G7) AS G7, SUM(DD_PLAN_1_STA_ENROLLMENT.G8) AS G8," +
" SUM(DD_PLAN_1_STA_ENROLLMENT.G9) AS G9, SUM(DD_PLAN_1_STA_ENROLLMENT.G10) AS G10, SUM(DD_PLAN_1_STA_ENROLLMENT.G11) AS G11, SUM(DD_PLAN_1_STA_ENROLLMENT.G12) AS G12 FROM DD_PLAN_1_STA_ENROLLMENT GROUP BY DD_PLAN_1_STA_ENROLLMENT.SCHL_ATND"}) AS DD_ENROLLMENT ON DD_PLAN_1_STUDENT_COUNT.SCHL_NAME = DD_ENROLLMENT.SCHOOL",
SubFields = "DD_PLAN_1_STUDENT_COUNT.SCHL_NAME AS SCHOOL, " +
"DD_PLAN_1_STUDENT_COUNT.GRD_RANGE AS GRADES_SERVED, " +
"DD_ENROLLMENT.TOTALENROLL + DD_PLAN_1_STUDENT_COUNT.TOTAL AS ENROLLMENT, " +
"DD_PLAN_1_STUDENT_COUNT.[RS], " +
"DD_PLAN_1_STUDENT_COUNT.[OD_UM], " +
"DD_ENROLLMENT.PK6, " +
"DD_ENROLLMENT.GPK, DD_ENROLLMENT.GK, " +
"DD_ENROLLMENT.G1, DD_ENROLLMENT.G2, " +
"DD_ENROLLMENT.G3, DD_ENROLLMENT.G4, DD_ENROLLMENT.G5, " +
"DD_ENROLLMENT.G6, DD_ENROLLMENT.G7, " +
"DD_ENROLLMENT.G8, DD_ENROLLMENT.G9, " +
"DD_ENROLLMENT.G10, DD_ENROLLMENT.G11, DD_ENROLLMENT.G12",
WhereClause = schoolClause
};

It throws errors: invalid SQL statement. How can I resolve this issue using ArcGIS Pro add-ins in WPF?