Why does CIMSqlQueryDataConnection.FromJson choke on queryFields?

[KirkKuykendall1]

I was under the impression that I can always deserialize CIM objects using json copied from a .lyrx file. 

Is this true?

I created an SQL Query Layer with the UI, then saved it to a .lyrx file (see attached).

I then created a new file, sqldataconn1.json (see attached), with this json copied from the lyrx file:

{
    "type" : "CIMSqlQueryDataConnection",
	"workspaceConnectionString" : "SERVER=localhost;INSTANCE=sde:...",
	"workspaceFactory" : "SDE",
	"dataset" : "db1.DBO.%mytest2",
	"datasetType" : "esriDTFeatureClass",
	"sqlQuery" : "select OBJECTID,SHAPE,GDB_GEOMATTR_DATA from db1.dbo.TEST2",
	"srid" : "3857",
	"spatialReference" : {
	"wkid" : 102100,
	"latestWkid" : 3857
	},
	"oIDFields" : "OBJECTID",
	"geometryType" : "esriGeometryPolygon",
	"extent" : {
	"xmin" : -11607361.9631999992,
	"ymin" : 3260858.89570000023,
	"xmax" : -10527607.362,
	"ymax" : 3930306.748499997,
	"spatialReference" : {
	  "wkid" : 102100,
	  "latestWkid" : 3857
	}
	},
	"queryFields" : [
	{
	  "name" : "OBJECTID",
	  "type" : "esriFieldTypeInteger",
	  "alias" : "OBJECTID"
	},
	{
	  "name" : "SHAPE",
	  "type" : "esriFieldTypeGeometry",
	  "alias" : "SHAPE",
	  "geometryDef" : {
		"avgNumPoints" : 0,
		"geometryType" : "esriGeometryPolygon",
		"hasM" : false,
		"hasZ" : false,
		"spatialReference" : {
		  "wkid" : 102100,
		  "latestWkid" : 3857
		}
	  }
	},
	{
	  "name" : "GDB_GEOMATTR_DATA",
	  "type" : "esriFieldTypeBlob",
	  "alias" : "GDB_GEOMATTR_DATA"
	}
	],
	"spatialStorageType" : 2
}

However I get an exception:

"Cannot cast Newtonsoft.Json.Linq.JArray to Newtonsoft.Json.Linq.JToken."

If I remove the queryFields element from the json, sqldataconn2.json (see attached), it runs without error.

[UmaHarano]

Hi,

How are you using the JSON strings to create the new layer?

It is best to use the LayerDocument to accomplish this . LayerDocument gives you the ability to work with an "in-memory" representation of the layer. You can configure it (data connection, SQL query, renderers, etc) and then add it to the map. You can use an existing LayerX file to create a layer document object, and then access its CIM Definition to configure it.

 QueuedTask.Run( () => {
        //Creates an in memory copy of the existing lyrx file
        ArcGIS.Desktop.Mapping.LayerDocument lyrDoc = new ArcGIS.Desktop.Mapping.LayerDocument(@"C:\pathtoLayerXFile\layer.lyrx");
        var cimLayerDoc = lyrDoc.GetCIMLayerDocument();
        //Configure any aspects of the layer you want here. Query definitions, etc.
        cimLayerDoc.LayerDefinitions[0].Name = "MyNewLayer";
        //Create the layer in the active map using the layer document.
        var layerParams = new LayerCreationParams(cimLayerDoc);
        LayerFactory.Instance.CreateLayer<FeatureLayer>(layerParams, MapView.Active.Map,
                                                                         LayerPosition.AddToTop);
      });

Here is a ProSnippet that also addresses this:

Create layer from a lyrx file 

[KirkKuykendall1]

Hi Uma -

Thanks for responding.  My question is mainly how CIM deserialization works. I am able to create a featurelayer from an lyrx file as you describe.

So far I have assumed that any json found in a file (aprx, lyrx, etc.) could be deserialized by the FromJson static method on the CIM class. 

For this specific use case though, suppose I have a 3rd party (non-Esri) table with geometry columns, and want to dynamically create an SQL query layer.  Can I safely do that without writing out an lyrx file?

If so, how does the absence of the queryFields array impact the resulting layer?

Kirk

[UmaHarano · Accepted solution]

Hi Kirk

I see the issue you are experiencing. Adding the queryFields array causes the exception. The workflow is successful if I remove the queryFields array (the SQL query is still successfully executed).  The table gets successfully added to the map. I couldn't see any impact in the table added to the map. 

Please remove the queryFields array in your JSON. In the meantime, I will follow-up and see why queryFields inclusion is causing the crash.

Thanks for reporting this!

Uma