How to perform join and query with composite keys

[Anonymous User]

Hi Guys,

I have basin feature class, platform table in my geodatabase.

They have composite keys relationships, (meaning two keys as primary keys)

Example sql query statement I want to perform in sdk is  select p.id1, p.basinname, p.segmentname from platform p LEFT JOIN basin b ON p.id1 = b.id1 and p.id2 = b.id2 where p.category = 'thirdparty'

Refer to that statement id1, id2 are the composite keys.

How can I do this in arcgis pro sdk?

I tried and referred to sdk doco geodatabase api => ProConcepts Geodatabase · Esri/arcgis-pro-sdk Wiki · GitHub 

And I can't figure it out how to manage with arcgis pro sdk?

[RichRuh · Accepted solution]

Hi Than,

I think you want to take a look at QueryDefs (ProConcepts Geodatabase · Esri/arcgis-pro-sdk Wiki · GitHub ).  This is another join construct in ArcGIS.  It works with datasets that come from the same geodatabase.  

There are a lot of different query constructs available in the Pro SDK.  In addition to just a basic query on a single table using Table.Search, we support QueryDefs, Joins, QueryTables, and QueryLayers.  If the document above doesn't clarify the difference enough, my colleagues and I gave a session on the Geodatabase SDK at the (virtual) DevSummit this year: https://www.youtube.com/watch?v=EKx1kzQARlM&list=PLaPDDLTCmy4Ys8vfmC7DbX3FHSsyosvh7&index=151&t=0s  

The section on Queries starts at 14:40.

I hope this helps,

--Rich

[Anonymous User]

Thank Rich Ruh‌,

Look like I have to read through whole block, that QueryDef object solve that composite join issue.

I am so much focus on Joint section in the article and miss that section.

Below is the working code snippet, incase if someone encounter similar situation.

gdb.Evaluate does not work for me, not sure why but It does work with opentable and search function combination.

  QueryDef queryDef = new QueryDef
                        {
                            Tables = tableJoinStatement,
                            SubFields = retrieveSelect,
                            WhereClause = this._CurrentWhereClause
                        };

                        QueryTableDescription queryTableDescription = new QueryTableDescription(queryDef)
                        {
                            Name = "PlatformBasinJoin"

                        };‍‍‍‍‍‍‍‍‍‍‍‍

Table queryTable = gdb.OpenQueryTable(queryTableDescription);
                        using (RowCursor rowCursor = queryTable.Search())
                        //using (RowCursor rowCursor = gdb.Evaluate(queryDef, false))//does not work, expected field not found error
                        {
                            while (rowCursor.MoveNext())
                            {
                                using (Row row = rowCursor.Current)
                                {


............‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍