Runtime Workaround #3: Querying M:N Relationships
Well! In my previous article, I presented a workaround for a bizarre MMPK bug that reappeared in version 100.5 of the ArcGIS Runtime SDK for .NET; but just when I thought I couldn't find another one even more unthinkably bizarre, up pops this: QueryRelatedFeaturesAsync will return a bad result when the FeatureLayer of a GeodatabaseFeatureTable is a sublayer of a GroupLayer.
Huh? You can check out the attached Visual Studio project for confirmation, but in the meantime we have a conundrum. It would appear that, just when the GroupLayer class is finally implemented, we have to chuck it right back out until a safer, more effective version is delivered. I've updated an earlier article of mine to reflect that situation. Nonetheless, I got to thinking about how Runtime support for querying M:N relationships in a mobile map package didn't even start to appear until 100.4, and what I would need to do in order to support them were I still stuck at 100.3. Or, what if QueryRelatedFeaturesAsync were to fail again in a future version?
Supporting one-to-one and one-to-many relationships is actually fairly simple, since the RelationshipInfo class gives the required information, when retrieved from both origin and destination tables. But many-to-many relationships are entirely another can of worms, because some crucial information is inaccessible via Runtime, even though it's encoded in the geodatabase.
Contrary to the wording in the documentation for the RelationshipInfo class [and I quote: "A relationship consists of two and only two tables"], M:N relationships involve a third, intermediate table. Querying M:N relationships requires knowledge to query that intermediate table, and that's precisely the information which is withheld from the Runtime developer.
Let's take a look at how relationships are stored in a mobile map package. In my previous article, I introduced you to the GDB_ServiceItems table. The ItemInfo field in that table stores the JSON data used to hydrate the ArcGISFeatureLayerInfo class:
Here's the JSON that describes the RegulatorStation to GasValve relationship from the origin role:
{
"id": 4,
"name": "Gas Valve",
"relatedTableId": 10,
"cardinality": "esriRelCardinalityManyToMany",
"role": "esriRelRoleOrigin",
"keyField": "OBJECTID",
"composite": false,
"relationshipTableId": 73,
"keyFieldInRelationshipTable": "REGSTATIONOBJECTID"
}
And here's the description for destination role:
{
"id": 4,
"name": "Regulator Station",
"relatedTableId": 13,
"cardinality": "esriRelCardinalityManyToMany",
"role": "esriRelRoleDestination",
"keyField": "OBJECTID",
"composite": false,
"relationshipTableId": 73,
"keyFieldInRelationshipTable": "GASVALVEOBJECTID"
}
The two crucial items that are not included in the RelationshipInfo class are relationshipTableId and keyFieldInRelationshipTable. But how to get at that information in your app? Aye, there's the rub. In short, you need to extract the geodatabase from the mobile map package and query the GDB_ServiceItems table directly. That's where you need a library such as System.Data.SQLite, which is available via the NuGet Package Manager:
Given the necessary tools, the first step is to extract the geodatabase to a temporary location:
public async Task Init(string sMMPKPath, Geodatabase gdb)
{
string sGDBPath = gdb.Path;
string sGDBName = Path.GetFileName(sGDBPath);
string sTempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
Directory.CreateDirectory(sTempDir);
string sTempPath = Path.Combine(sTempDir, sGDBName);
using (ZipArchive zip = ZipFile.OpenRead(sMMPKPath))
{
ZipArchiveEntry zipEntry = zip.GetEntry(sGDBPath);
zipEntry.ExtractToFile(sTempPath);
}
Next, query the desired information, taking the steps necessary to clean up afterwards:
List<string> ItemInfos = new List<string>();
string sConn = "Data Source=" + sTempPath + ";Read Only=True";
string sSQL = "SELECT ItemInfo FROM GDB_ServiceItems";
using (SQLiteConnection sqlConn = new SQLiteConnection("Data Source=" + sTempPath))
{
sqlConn.Open();
using (SQLiteCommand sqlCmd = new SQLiteCommand(sSQL, sqlConn))
{
using (SQLiteDataReader sqlReader = sqlCmd.ExecuteReader())
{
while (sqlReader.Read())
ItemInfos.Add(sqlReader.GetString(0));
sqlReader.Close();
}
}
sqlConn.Close();
}
GC.Collect();
GC.WaitForPendingFinalizers();
Directory.Delete(sTempDir, true);
Finally, combine the missing ingredients with the out-of-the-box information:
_infos = new Dictionary<long, Dictionary<long, ExtendedRelationshipInfo>>();
foreach (string sInfo in ItemInfos)
{
Dictionary<string, object> info = _js.DeserializeObject(sInfo) as Dictionary<string, object>;
if (!info.ContainsKey("relationships"))
continue;
object[] relationships = info["relationships"] as object[];
if (relationships.Length == 0)
continue;
long iTableID = Convert.ToInt64(info["id"]);
// Get basic table relationship infos
GeodatabaseFeatureTable gfTab = gdb.GeodatabaseFeatureTable(iTableID);
if (gfTab.LoadStatus != Esri.ArcGISRuntime.LoadStatus.Loaded)
await gfTab.LoadAsync();
Dictionary<long, RelationshipInfo> BasicInfos = new Dictionary<long, RelationshipInfo>();
foreach (RelationshipInfo relInfo in gfTab.LayerInfo.RelationshipInfos)
BasicInfos[relInfo.Id] = relInfo;
// Add extended data
Dictionary<long, ExtendedRelationshipInfo> ExtendedInfos = new Dictionary<long, ExtendedRelationshipInfo>();
foreach (object obj in relationships)
{
Dictionary<string, object> rel = obj as Dictionary<string, object>;
long iRelID = Convert.ToInt64(rel["id"]);
string sCard = rel["cardinality"].ToString();
long? iRelTableID = null;
string sKeyField = null;
if (sCard == "esriRelCardinalityManyToMany")
{
iRelTableID = Convert.ToInt64(rel["relationshipTableId"]);
sKeyField = rel["keyFieldInRelationshipTable"].ToString();
}
ExtendedRelationshipInfo erInfo = new ExtendedRelationshipInfo()
{
BasicInfo = BasicInfos[iRelID],
RelationshipTableId = iRelTableID,
KeyFieldInRelationshipTable = sKeyField
};
ExtendedInfos[iRelID] = erInfo;
}
_infos[iTableID] = ExtendedInfos;
} // foreach
Here, then, is the code for querying related features:
public async Task<FeatureQueryResult> QueryRelated(ArcGISFeature feat, long iRelID)
{
// Get relationship data
if (!(feat.FeatureTable is GeodatabaseFeatureTable gfTabSource))
return null;
long iTableID = gfTabSource.LayerInfo.ServiceLayerId;
if (!_infos.ContainsKey(iTableID))
return null;
Dictionary<long, ExtendedRelationshipInfo> ExtendedInfos = _infos[iTableID];
if (!ExtendedInfos.ContainsKey(iRelID))
return null;
ExtendedRelationshipInfo extInfoSource = ExtendedInfos[iRelID];
RelationshipInfo infoSource = extInfoSource.BasicInfo;
long iRelTableID = infoSource.RelatedTableId;
if (!_infos.ContainsKey(iRelTableID))
return null;
ExtendedInfos = _infos[iRelTableID];
if (!ExtendedInfos.ContainsKey(iRelID))
return null;
ExtendedRelationshipInfo extInfoTarget = ExtendedInfos[iRelID];
RelationshipInfo infoTarget = extInfoTarget.BasicInfo;
// Build query
string sKeyValSource = feat.GetAttributeValue(infoSource.KeyField).ToString();
Geodatabase gdb = gfTabSource.Geodatabase;
GeodatabaseFeatureTable gfTabTarget = gdb.GeodatabaseFeatureTable(iRelTableID);
string sKeyFieldTarget = infoTarget.KeyField;
Field fieldKeyTarget = gfTabTarget.GetField(sKeyFieldTarget);
StringBuilder sb = new StringBuilder();
sb.Append(sKeyFieldTarget);
if (infoSource.Cardinality == RelationshipCardinality.ManyToMany)
{
// Gather key values from intermediate table
GeodatabaseFeatureTable gfTabRel = gdb.GeodatabaseFeatureTable(extInfoSource.RelationshipTableId.Value);
string sKeyFieldRelSource = extInfoSource.KeyFieldInRelationshipTable;
Field fieldRelSource = gfTabRel.GetField(sKeyFieldRelSource);
string sWhere = sKeyFieldRelSource + " = " + sKeyValSource;
if (fieldRelSource.FieldType == FieldType.Guid)
sWhere = sKeyFieldRelSource + " = '" + sKeyValSource + "'";
QueryParameters qpRel = new QueryParameters() { WhereClause = sWhere };
FeatureQueryResult resultRel = await gfTabRel.QueryFeaturesAsync(qpRel);
if (resultRel.Count() == 0)
return resultRel;
string sKeyFieldRelTarget = extInfoTarget.KeyFieldInRelationshipTable;
Field fieldRelTarget = gfTabRel.GetField(sKeyFieldRelTarget);
sb.Append(" IN ( ");
bool bFirst = true;
foreach (Feature featRel in resultRel)
{
if (bFirst)
bFirst = false;
else
sb.Append(", ");
string sKeyValTarget = featRel.GetAttributeValue(sKeyFieldRelTarget).ToString();
if (fieldRelTarget.FieldType == FieldType.Guid)
sb.Append("'" + sKeyValTarget + "'");
else
sb.Append(sKeyValTarget);
}
sb.Append(" ) ");
}
else
{
sb.Append(" = ");
if (fieldKeyTarget.FieldType == FieldType.Guid)
sb.Append("'" + sKeyValSource + "'");
else
sb.Append(sKeyValSource);
}
// Query related features
QueryParameters qp = new QueryParameters() { WhereClause = sb.ToString() };
return await gfTabTarget.QueryFeaturesAsync(qp);
}
Needless to say, this is a pretty extreme approach to take. Nonetheless, you never know when this knowledge may come in useful.
UPDATE:
It occurred to me that since I routinely automate MMPK creation using Python, I could also create companion files containing the many-to-many relationships. I've added a new attachment that contains both a Python script and a revised version of the RelationshipHelper class that takes advantage of it. Now it's more feasible to support both group layers and related feature queries.