A recent question on SharePoint Stack Exchange inspired me to want to learn more about interacting with SharePoint lists using PowerShell, especially external lists created through Business Connectivity Services (BCS) in SharePoint 2010. With my background being more in .NET development using the object model, I was surprised to discover some of the inconsistencies one sees when interacting with an external list in PowerShell (compared with a standard out-of-the-box list).

For the purposes of this discussion, I will assume that you are familiar with how to use SharePoint Designer to create an external content type mapped to a SQL Server data source. From there, I created an external list for this external content type.

What initially made my learning journey such a confusing one was that the SQL Server table I used as the data source for my external content type contained a column named ID of type integer. It is possible that any external line-of-business (LOB) data source you consume through BCS may have a similar column as well. The values I initially inserted into the database table had auto-increment values of 1, 2, 3, etc. This enabled me to interact with the items in my external list using the following PowerShell syntax:

The first two lines may be unfamiliar to those of you who have not programmed with BCS before. With BCS, it is necessary to establish an SPServiceContext within an SPServiceContextScope that represents the HTTP context of the Business Data Connectivity Service.

If you ever see the following error:

“The shim execution failed unexpectedly – Proxy creation failed. Default context not found.”

Make sure you instantiate an SPServiceContext within an SPServiceContextScope as I have above.

It appears that there are some things you can do without properly obtaining the SPServiceContext within an SPServiceContextScope (such as enumerating the Fields collection of the external list). That said, the safest approach appears to be to always instantiate the SPServiceContextScope as shown above. If anyone can shed any additional light on why this is, please feel free to share in the comments below!

Fun Fact: An ID is not always an ID!

Here is my external list, viewed through the browser:

As you can see, the list contains an ID column with integer values, mapped directly to the similarly configured column in my SQL Server database table:

This allows me to run the following script to obtain access to the item with an ID value of 2:

Seems reasonable, right? After all, the second item we insert into a new out-of-the-box SharePoint list would be assigned an ID value of 2 as well (without us having to do anything to make it happen). And since I was able to call GetItemById(2), that must mean the ID property of the list item is 2, right?

Wrong!

Well, this is interesting. It turns out that my call to GetItemById(2) only works for the following reasons:

• My external list contains a column named ID.
• That column is of data type integer.

For instance, I cannot create another external list mapped to a data source with a column named ID but of a different type (i.e., nchar(10)), even if that column only contains integer values. Here’s what happens in that case:

Of course, it goes without saying that for an out-of-the-box list, the behavior is as you would expect:

Another item of interest is that the ItemCount property of external lists always returns 0, no matter how many items are in the list:

If you have any fun stories or observations about dealing with external lists in PowerShell, please feel free to share them in the comments below!