Joining Table to Feature

[GeorgeFaraj]

I use the following code to do a join but when this routine has completed without error my featureLayer still has a FeatureClass that looks the same as before I run this code. What does the DisplayRelationshipClass() call actually do? I see the help talking about creating an IRelQueryTable under the covers but if this code is not going to update the information that my featureLayer returns then it is not really joining anything. How can I get an actual join?

private void JoinTableToFeature( IFeatureLayer featureLayer, ITable externalTable ) {

    IGeoFeatureLayer geoFeatureLayer = featureLayer as IGeoFeatureLayer;
    IMemoryRelationshipClassFactory memoryRelationshipFactory = null;

    try {
        Type memoryRelationshipClassFactory = Type.GetTypeFromProgID( "esriGeodatabase.MemoryRelationshipClassFactory" );
        memoryRelationshipFactory = Activator.CreateInstance( memoryRelationshipClassFactory )
            as IMemoryRelationshipClassFactory;

        IRelationshipClass relationshipClass =
            memoryRelationshipFactory.Open(
                "Join",
                externalTable as IObjectClass,
                EXTERNAL_KEY,
                geoFeatureLayer.DisplayFeatureClass as IObjectClass,
                FEATURE_KEY,
                "forward",
                "backward",
                esriRelCardinality.esriRelCardinalityOneToOne );

        IDisplayRelationshipClass displayRelationshipClass = featureLayer as IDisplayRelationshipClass;
        displayRelationshipClass.DisplayRelationshipClass( relationshipClass, esriJoinType.esriLeftOuterJoin );
    }
    finally {

        Marshal.FinalReleaseComObject( memoryRelationshipFactory );
    }
}

[NeilClemmons]

Performing a join doesn't change the underlying feature class.  If it did, then that means it would be changing your actual data (if you want the join to be permanent then you will need to export the layer out to a new feature class).  A feature layer is based on a data source (i.e. a feature class).  You can add definition queries, joins, relates, etc. but none of this should be changing the actual data source; it should simply change how the layer is displayed.  When you query the layer, you have several options.  If you want your query to return results from the feature class then use IFeatureLayer.FeatureClass.Search to execute the query.  The query will not be filtered by any definition query and will not contain fields from any joins (because it's querying the actual data in the database, not the visual representation).  If you want the query to respect the definition query then use IFeatureLayer.Search to execute the query.  The features returned by this query will not contain fields from any joins.  If you want the query to respect the definition query and also contain fields from any joins then use IGeoFeatureLayer.SearchDisplayFeatures or IDisplayTable.SearchDisplayTable to execute the query.

[GeorgeFaraj]

Performing a join doesn't change the underlying feature class.  If it did, then that means it would be changing your actual data (if you want the join to be permanent then you will need to export the layer out to a new feature class).  A feature layer is based on a data source (i.e. a feature class).  You can add definition queries, joins, relates, etc. but none of this should be changing the actual data source; it should simply change how the layer is displayed.  When you query the layer, you have several options.  If you want your query to return results from the feature class then use IFeatureLayer.FeatureClass.Search to execute the query.  The query will not be filtered by any definition query and will not contain fields from any joins (because it's querying the actual data in the database, not the visual representation).  If you want the query to respect the definition query then use IFeatureLayer.Search to execute the query.  The features returned by this query will not contain fields from any joins.  If you want the query to respect the definition query and also contain fields from any joins then use IGeoFeatureLayer.SearchDisplayFeatures or IDisplayTable.SearchDisplayTable to execute the query.

I changed my Unique Renderer to use my DisplayTable like this:

    IDisplayTable displayTable = classifyParms.FeatureLayer as IDisplayTable;
    ITable table = displayTable.DisplayTable;
    ITableSort tableSort = new TableSortClass();
    tableSort.Table = table;
    tableSort.Fields = classifyParms.ClassifyField.Qualified;
    tableSort.set_Ascending( classifyParms.ClassifyField.Qualified, true );
    tableSort.Sort( null );
    cursor = tableSort.Rows as ICursor;

so I could iterate using the cursor. The sort doesn't work (I don't know why) but I can iterate and access the data correctly to add the unique values to my renderer.  The problem is that the values are not being matched on the map (the map stays one color.)  Shouldn't the map use the DisplayTable data to select the correct symbology? (That makes sense to me but I'm not seeing that.)

[NeilClemmons]

Can you post the code that creates the unique value renderer and applies it to the layer?  Also, what is the value of Fields after this line executes:

tableSort.Fields = classifyParms.ClassifyField.Qualified;

[GeorgeFaraj]

Can you post the code that creates the unique value renderer and applies it to the layer?  Also, what is the value of Fields after this line executes:
tableSort.Fields = classifyParms.ClassifyField.Qualified;

// private because CreateRenderer() picks the correct method based on the settings in classifyParms
private IFeatureRenderer CreateUniqueBreaksRenderer( GisClassifyParms classifyParms ) {
    IUniqueValueRenderer uniqueValueRenderer = new UniqueValueRendererClass();
    ICursor cursor = null;
    try {
        // this either calls a SQL stored procedure Count or IDataStatistics methods to get a count of unique values
        int classCount = GetDistinctCount( classifyParms, classifyParms.ClassifyField.Name );
        IDisplayTable displayTable = classifyParms.FeatureLayer as IDisplayTable;
        ITable table = displayTable.DisplayTable;
        ITableSort tableSort = new TableSortClass();
        tableSort.Table = table;
        tableSort.Fields = classifyParms.ClassifyField.Qualified;
        tableSort.set_Ascending( classifyParms.ClassifyField.Qualified, true );
        tableSort.Sort( null );
        cursor = tableSort.Rows as ICursor;
        uniqueValueRenderer.FieldCount = 1;
        uniqueValueRenderer.set_Field( 0, classifyParms.ClassifyField.Qualified );
        // creates the correct size of color ramp (StyleItem is ramp)
        IEnumColors enumColors = GetColorEnum( classifyParms.StyleItem, classCount );
        if ( enumColors == null ) return null;
        bool isFound = false;
        int index = 0;
        int fieldIndex = cursor.Fields.FindField( classifyParms.ClassifyField.Qualified );
        object currentValue;
        string currentBreak = "";
        IFeature nextFeature = cursor.NextRow() as IFeature;
        IRow nextRow = cursor.NextRow();
        while ( nextRow != null ) {
            ISymbol symbol = null;
            currentValue = nextRow.get_Value( fieldIndex );
            currentBreak = ( currentValue == null ) ? "" : currentValue.ToString(); 
            isFound = false;
            for ( int i = 0; i <= uniqueValueRenderer.ValueCount - 1; i++ ) {
                if ( uniqueValueRenderer.get_Value( i ) == currentBreak ) {
                    isFound = true;
                    break;
                }
            }
            if ( !isFound ) {
                Debug.Print( "Break: {0}", currentBreak );
                // creates simple fill symbol with passed color and line width
                symbol = GetFillSymbol( symbol as ISimpleFillSymbol, enumColors.Next(), 1 );
                uniqueValueRenderer.AddValue( currentBreak, classifyParms.ClassifyField.Name, symbol );
                uniqueValueRenderer.set_Label( currentBreak, currentBreak );
                uniqueValueRenderer.set_Symbol( currentBreak, symbol as ISymbol );
            }
            nextRow = cursor.NextRow();
            index++;
        }
    }
    catch ( COMException cex ) {
        Debug.Print( "CreateUniqueBreaksRenderer() Error: {0}", cex.Message );
    }
    catch ( Exception ex ) {
        Debug.Print( "CreateUniqueBreaksRenderer() Error: {0}", ex.Message );
    }
    finally {
        Marshal.FinalReleaseComObject( cursor );
    }
    return uniqueValueRenderer as IFeatureRenderer;
}

I left out most of the debugging code (except try and print.)

GisClassifyParms is a data class containing the parameters for a classification renderer including a GisField that stores field information like a name, alias, heading, and the qualified name (which is updated with the correct qualified field name after the join is executed.)

There is no accessor for ITableSort.Fields but my qualified field name is: "databasename.DBO.S.test_cd"
There are 22 fields in my feature layer and the join adds the key and the test_cd to the DisplayTable (they become fieldindex 22 and 23.)
"test_cd" has 231 unique values and is distributed around the map (that has about 50,000 polygons) in chunks (but the map after this code is one color.)
I have an identity command that reads those values and this is what drives me crazy: the layer tells me it only has 22 fields but when do the IdentifyObj dance the row that results is able to read column 22 and 23 and show the color that the unique renderer should be using.

[NeilClemmons]

It's been a while since I've had to write any code that deals with joins but your fully qualified field name doesn't look correct to me.  I've looked over some of our code that deals with qualified field names and all it appears to be doing is appending the table name to the field name (table.field).  Your field name has the database name and owner name appended as well.  Try stripping that off and see what happens.

[GeorgeFaraj]

It's been a while since I've had to write any code that deals with joins but your fully qualified field name doesn't look correct to me.  I've looked over some of our code that deals with qualified field names and all it appears to be doing is appending the table name to the field name (table.field).  Your field name has the database name and owner name appended as well.  Try stripping that off and see what happens.

I'll try that but the field name I'm using is the one that shows up in the .Fields collection after the join.

Changing the Qualified just produces 0x80004005 (E_FAIL). It makes sense that the name used in the internal collection should be correct but likewise it makes sense that the DisplayTable data should be used to assign the correct rendering symbol (of course that's not happening...)

[GeorgeFaraj]

I'll try that but the field name I'm using is the one that shows up in the .Fields collection after the join.

Changing the Qualified just produces 0x80004005 (E_FAIL). It makes sense that the name used in the internal collection should be correct but likewise it makes sense that the DisplayTable data should be used to assign the correct rendering symbol (of course that's not happening...)

I still don't understand how a join that does not show up in the featurelayer is supposed to help me. I added my labeling methods to my test code and ran a join and label - no labels! Why is the DisplayTable not being used? What is the purpose of a a DisplayTable? What is the relationship of a DisplayTable to a FeatureLayer? (We're living in a world of confusion - at least I am.)

[NeilClemmons]

I put together a quick sample that is working for me when I run it.

        Try
            ' Open the geodatabase.
            Dim workspacePath As String = "C:\Development\ApplicationTest\Data\Gdb\test.mdb"
            Dim workspaceFactory As IWorkspaceFactory = DirectCast(Activator.CreateInstance(Type.GetTypeFromProgID("esriDataSourcesGDB.AccessWorkspaceFactory")), IWorkspaceFactory)
            Dim workspace As IWorkspace = workspaceFactory.OpenFromFile(workspacePath, m_application.hWnd)

            ' Open the feature class.
            Dim featureClassName As String = "structure_existing_area"
            Dim featureClass As IFeatureClass = DirectCast(workspace, IFeatureWorkspace).OpenFeatureClass(featureClassName)

            ' Open the table.
            Dim tableName As String = "building_types"
            Dim table As ITable = DirectCast(workspace, IFeatureWorkspace).OpenTable(tableName)

            ' Create a layer from the feature class.
            Dim featureLayer As IFeatureLayer = New FeatureLayer
            featureLayer.Name = "Buildings"
            featureLayer.FeatureClass = featureClass

            ' Get the fields to use in the join.
            Dim tableField As IField = table.Fields.Field(table.Fields.FindField("building_type"))
            Dim layerField As IField = featureClass.Fields.Field(featureClass.Fields.FindField("buildng_id"))

            ' Join the table to the layer.
            Dim memRelClassFactory As IMemoryRelationshipClassFactory = New MemoryRelationshipClassFactory
            Dim relClass As IRelationshipClass = memRelClassFactory.Open("Join", DirectCast(table, IObjectClass), tableField.Name, featureLayer.FeatureClass, layerField.Name, "forward", "backward", esriRelCardinality.esriRelCardinalityOneToMany)
            Dim displayRelClass As IDisplayRelationshipClass = DirectCast(featureLayer, IDisplayRelationshipClass)
            displayRelClass.DisplayRelationshipClass(relClass, esriJoinType.esriLeftOuterJoin)

            ' Create the renderer.
            Dim renderer As IUniqueValueRenderer = New UniqueValueRenderer
            renderer.FieldCount = 1
            renderer.Field(0) = DirectCast(table, IDataset).Name & "." & tableField.Name

            ' Add values and symbols to the renderer.
            Dim featureCursor As IFeatureCursor = DirectCast(featureLayer, IGeoFeatureLayer).SearchDisplayFeatures(Nothing, True)
            Dim dataStats As IDataStatistics = New DataStatistics
            dataStats.Field = tableField.Name
            dataStats.Cursor = DirectCast(featureCursor, ICursor)
            Dim values As IEnumerator = dataStats.UniqueValues
            values.Reset()
            Dim count As Int32 = 0
            Do While values.MoveNext
                Dim value As String = Convert.ToString(values.Current)

                ' There are only 3 unique values in the table so I'm just hard-coding some colors for this sample.
                Dim symbol As ISimpleFillSymbol = New SimpleFillSymbol
                Dim color As IRgbColor = New RgbColor
                If count = 0 Then
                    color.Red = 255
                ElseIf count = 1 Then
                    color.Green = 255
                Else
                    color.Blue = 255
                End If
                symbol.Color = color
                count += 1

                renderer.AddValue(value, "Building Type", DirectCast(symbol, ISymbol))
            Loop

            ' Set the default symbol but don't use it.
            renderer.DefaultSymbol = New SimpleFillSymbol
            renderer.UseDefaultSymbol = False

            DirectCast(featureLayer, IGeoFeatureLayer).Renderer = DirectCast(renderer, IFeatureRenderer)

            ' Add the layer to the map.
            DirectCast(m_application.Document, IMxDocument).FocusMap.AddLayer(featureLayer)
            DirectCast(m_application.Document, IMxDocument).UpdateContents()
            DirectCast(m_application.Document, IMxDocument).ActiveView.Refresh()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

[GeorgeFaraj]

So I did this exact same thing (only with a SQL Server table and an existing feature layer) and indeed I get a feature layer that shows visible selections but it doesn't show different symbols.  I thought the whole point of the "Display" classes was to serve as the "visible" data.  But that is not what I am seeing. When I complete the join I am left with 2 more fields in my DisplayFeatureClass and while the cursor and statistics seem happy to use the "Display" class and the extra fields. When the features are evaluated for symbol selection it is not using that data. It seems like the only way I can use that data is to assign it to a FeatureClass. Can someone from ESRI weigh in on this? What is actually used for processing - the DisplayFeatureClass or the FeatureClass? And how should joined data be shared?

And - if I modify the code to build an IRelQueryTable and assign that to the new feature layer's FeatureClass then it does the unique symbology correctly but the selections are hidden. Argggghhhh.