SharePoint 2013 External Content type. Populate secondary fields

Question

I am trying to migrate data to a document library that contains a external content type using the SharePoint 2013 client object model. The external content type is pulling from a sql server 2012 table.

I can populate the external data column and the secondary fields using a brute force method shown below. However, I would like to trigger an event where sharepoint updates the field for me. Right now, I validate the values by querying the LOB system and then populating the fields that were provided to be visible.

I am having issues when I change or add a filter. Currently I am populating the Entity lookup from a FilterCollection object. How do I get an unbounded list of the LOB FieldValues?

I have read that Metavis! doesn't provide this ability in their migration tool and that it might be a server side only available solution.

Please provide any help

The following is a snippet of what I have so far. BusinessDataField is an internal domain object.

 Microsoft.SharePoint.Client.Web web = clientContext.Web;
  Microsoft.SharePoint.Client.List documentLibrary = clientContext.Web.Lists.GetByTitle(documentLibraryName);

  ListItem item = documentLibrary.GetItemById(itemID);
  string readListInstanceName ="Read List";
  List<string> externalData = new List<string>();
  foreach(BusinessDataField businessDataField in businessDataFields)
  {

    Microsoft.BusinessData.MetadataModel.Entity entity = clientContext.Web.GetEntity(businessDataField.EntityNamespace, businessDataField.EntityName);
    Microsoft.BusinessData.MetadataModel.LobSystem lobSystem = entity.GetLobSystem();
    Microsoft.BusinessData.MetadataModel.Collections.LobSystemInstanceCollection lobSystemInstanceCollection = lobSystem.GetLobSystemInstances();
    clientContext.Load(lobSystemInstanceCollection);
    clientContext.ExecuteQuery();
    Microsoft.BusinessData.MetadataModel.LobSystemInstance lobSystemInstance = lobSystemInstanceCollection[0];

    // get the filters for given name of method instance
    Microsoft.BusinessData.MetadataModel.Collections.FilterCollection fCollection = entity.GetFilters(readListInstanceName);
    // get the Line of Business data
    clientContext.Load(fCollection);
    clientContext.ExecuteQuery();
    foreach ( var filter in fCollection)
    {
      int count = filter.ValueCount;
    }
    Microsoft.BusinessData.MetadataModel.Collections.EntityInstanceCollection result = entity.FindFiltered(fCollection, readListInstanceName, lobSystemInstance);
    clientContext.Load(result);
    clientContext.ExecuteQuery();
    Dictionary<string,object> fields = null;
    foreach (var r in result )
    {
      var found = r.FieldValues.ContainsKey(businessDataField.BdcFieldName) && r.FieldValues.ContainsValue(businessDataField.FieldValue);
      if(found)
      {
        fields = r.FieldValues;
        foreach(var secondaryField in businessDataField.SecondaryFields)
        {
          string [] value = secondaryField.Value.Split(new char[]{'_'});
          string column = value.Last();
          foreach( var update in fields)
          {
            if(update.Key.ToUpper().Equals(column.ToUpper()))
            {
              item[secondaryField.Value] = update.Value;
              break;
            }                
          }             
        }
        break;
      }        
    }
    string encodedValue = EntityInstanceIdEncoder.EncodeEntityInstanceId(new object[] { businessDataField.FieldValue });
    item[businessDataField.RelatedFieldName] = encodedValue;
    item[businessDataField.FieldName] = businessDataField.FieldValue;
  }

  item.Update();
  clientContext.ExecuteQuery();

Answer 1

I got it to work with the following code snippet. The user passes in the value that i use to query the BCS to get the Secondary Field values. I parse the filters and dynamically set the filter values using the method from FilterCollection class SetFilterValue().

private static Microsoft.BusinessData.MetadataModel.Collections.EntityInstanceCollection GetSecondaryFieldValuesFromBCS(Microsoft.SharePoint.Client.ClientContext clientContext, BusinessDataField businessDataField)
    {
      string readListInstanceName = "Read List";
      Microsoft.BusinessData.MetadataModel.Entity entity = clientContext.Web.GetEntity(businessDataField.EntityNamespace, businessDataField.EntityName);
      Microsoft.BusinessData.MetadataModel.LobSystem lobSystem = entity.GetLobSystem();
      Microsoft.BusinessData.MetadataModel.Collections.LobSystemInstanceCollection lobSystemInstanceCollection = lobSystem.GetLobSystemInstances();
      clientContext.Load(lobSystemInstanceCollection);
      clientContext.Load(entity);
      clientContext.ExecuteQuery();

      Microsoft.BusinessData.MetadataModel.LobSystemInstance lobSystemInstance = lobSystemInstanceCollection[0];

      Microsoft.BusinessData.MetadataModel.Collections.FilterCollection externalFiltersOnFieldCollection = entity.GetFilters(readListInstanceName);
      List<string> searchFilters = new List<string>();
      clientContext.Load(externalFiltersOnFieldCollection);
      clientContext.ExecuteQuery();
      foreach(Microsoft.BusinessData.MetadataModel.Filter filter in externalFiltersOnFieldCollection)
      {
        if(filter.FilterField.Equals(businessDataField.BdcFieldName))
        {
          searchFilters.Add(filter.Name);
        }
      }
      foreach(string filterName in searchFilters)
      {
        externalFiltersOnFieldCollection.SetFilterValue(filterName, 0, businessDataField.FieldValue);
      }
      Microsoft.BusinessData.MetadataModel.Collections.EntityInstanceCollection result = entity.FindFiltered(externalFiltersOnFieldCollection, readListInstanceName, lobSystemInstance);
      clientContext.Load(result);
      clientContext.ExecuteQuery();
      return result;
    }

Lastly, I am still in need of setting the methodInstance dyanamically. The following line needs to be set dynamically,

string readListInstanceName = "Read List";

That value is passed into the following:

entity.FindFiltered(externalFiltersOnFieldCollection, readListInstanceName, lobSystemInstance);

Every example I have found is either hard coding it or is not using managed Client object model.