Programmatically populating a BDC field in Sharepoint 2010

In this post I try to explain how to retrieve and store data in a external lookup column or business data field. For the code snippets I use PowerShell instead of C# because I can test my claims as I write.

Lets set up some pseudo vars

$list=$web.Lists["ListWithExternalRef"]

$listItem=$list[0]

$bdcField=$list.Fields["ExternalItemField"]

$listItem["bdcColName"] holds the value of the BDC field which was specified when setting up the column, which can be set in SPBusinessDataField.BdcFieldName. The data is always stored as a string.

The BCS Identity of the item is stored in $listItem[$bdcField.RelatedField]. 

(Still need to check if this is EntityInstanceReference or Identity
Check BCSFieldEditor and ItemPicker with .NET Reflector )

If you want to deserialize the EntityInstanceReference you need to get an instance to the matching IMetadataCatalog :
$bdcService=(Get-SPService).services|?{$_.typename -eq "Business Data Connectivity Service"}

$bdcCat=$bdcService.GetDatabaseBackedMetadataCatalog((Get-SPServiceContext http://yourdevsite))

[Microsoft.BusinessData.Runtime.entityinstancereference]::Deserialize($bcsRef,$bdcCat)

For each additional external column which is stored with this field Sharepoint creates a field in the list. The additional fields names can be obtained with $bdcField.GetSecondaryFieldsNames(). To get a reference to the field you can use the display name which is in the  format "$($bdcField.Title): $secondaryFieldName". 

Sharepoint doesnt set the secondary lookup values itself when you save the entry, so you have to retrieve all values for the secondary field names.