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

[RejeanLabbe]

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.

[Aashis]

QueryDef is not supported in a feature service datastore.

[RejeanLabbe · Accepted solution]

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
              }
            }
          }
        }
      }
    }

[PARTHASARATHIU]

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?