How to create a BCS lookup field in a content type

Hellloooooo fellow frustrated SharePoint devs!
(Well I assume you are, otherwise you wouldn't be interested in this ...)

I'm currently working on a little project/proof-of-concept which involves heavy usage of the new SharePoint 2010 BCS functionalities. I've already come across a few bumps in the road for which I couldn't immediately find some useful info, so I guess this will be the first of many posts on this subject.

Today I'll start with the following issue: how to use a BCS lookup field in a content type.
Normally this shouldn't be to hard to do:

1. Create a site column "A"
2. Create a content type (on site collection level) "CT"
3. Add a reference to column "A" in content type "CT"

However, no matter how hard I looked, I just couldn't find an option to create a BCS lookup field as site column. Fyi: a BCS lookup field is called External Data field in the UI.
Sure, you can easily create such field on a list level, but not on a site level.

Adding a list BCS lookup. Notice the External Data type

Trying to add a site BCS lookup. No external data type here ... :(

So after googling around without any result, I found it was time to fumble around with my trusty Powershell and see how these list fields are defined.
Apparently the field's SchemaXml is:

<field
type="BusinessData"
displayname="bcs_field"
required="FALSE"
enforceuniquevalues="FALSE"
id="{GUID}"
sourceid="{GUID}"
staticname="bcs_field"
baserenderingtype="Text"
name="bcs_field"
colname="nvarchar11"
rowordinal="0"
version="3"
group=""
systeminstance="SPSDEV1"
entitynamespace="SPSDEV1.BCS"
entityname="dbo_Persons"
bdcfield="Names"
profile=""
hasactions="True"
addfieldoption="AddToAllContentTypes, AddFieldToDefaultView">
</field>

Notable properties are:

• SystemInstanceName: this is the BCS instance name.
  You can find this value via Central Admin > Application Management > Manage Service Applications > Business Data Connectivity Services. Then select External Systems and select the system's name. This should result in an URL like http://centraladmin/_admin/BDC/ViewBDCLobSystemInstances.aspx?AppId=...
  
  
• EntityNamespace: BCS entity's namespace
  
• EntityName: BCS entity you're referencing to
  
• BdcFieldName: the BCS field you want to display in the column

Those last three properties' values can be found in the External Content Types overview of Central Admin > Application Management > Manage Service Applications > Business Data Connectivity Services

Armed with this info, I cooked up this piece of code which will create a BCS lookup site column. Of course, you can then reuse this column in any content type.

private string CreateBCSLookupField(SPWeb targetWeb,
string lookupFieldName, string groupName,
string systemInstanceName, string entityNamespace,
string entityName, string entityFieldName, bool hasActions)
{
SPBusinessDataField lookupField =
targetWeb.Fields.CreateNewField("BusinessData", lookupFieldName) as SPBusinessDataField;
lookupField.Group = groupName;
lookupField.SystemInstanceName = systemInstanceName;
lookupField.EntityNamespace = entityNamespace;
lookupField.EntityName = entityName;
lookupField.HasActions = hasActions;
lookupField.BdcFieldName = entityFieldName;
return targetWeb.Fields.Add(lookupField);
} 

*UPDATE*
Okay, apparently there's still something fishy going on behind the scene. I've been using this code for a while now and it seems there's an inconsistency when you create multiple BCS site columns in a row.
Lets say you create 3 columns (A, B, C) with this code.

• First test:
  A was ok, B too, but C resulted in an error about an improperly configured lookup.
• Second test (same code, same setup):
  A was ok, B impropertly configged and C was ok
• Third test (again same setup):
  A failed, B was ok and C failed too

To be honest, I really don't know why it sometimes fails.  It's the same frikking code, but somehow SharePoint sometimes decides to funk things up.
Funking things up apparently comes down to bad property values.  I compared the schema xml's of properly created BCS fields and those that weren't: the correct fields had the same XML as I posted before, while the erronous fields had this xml

<Field  
Type="BusinessData"  
DisplayName="bcs_field"  
Required="FALSE"  
EnforceUniqueValues="FALSE"  
Group="BCS Test"  
ID="{GUID}"  
SourceID="{GUID}"  
StaticName="bcs_field"  
Name="bcs_field"  
BaseRenderingType="Text"  
ColName="nvarchar11"  
RowOrdinal="0" />

As you can see, this xml doesn't have the BCS related properties ... but why?!
Currently I only have one "workaround":

1. Create BCS field
2. Check if the resulting fields schema xml contains a reference to SystemInstance (or another BCS related property)
3. If it doesn't, remove the field and try again.  Usually it'll then be created properly
4. Rince and repeat if it still doesn't have a correct schema xml (of course, limit the number of repeats to prevent endless loops)

I know, it's definately not kosher ... but I just don't have a clue at the moment on what's causing these missing properties.
Any ideas are welcome :)

Querying SharePoint 2010 SPSite from within console application results in FileNotFoundException

Tried to connect to a SharePoint 2010 sitecollection via a console application today, but it kept failing with an FileNotFoundException.
Code:

using(SPSite site = new SPSite("http://sps2010"){
 //FileNotFoundException
}

Just for future reference:

1. make sure your application is using the .NET 3.5 framework
2. ensure it's compiled as 64 bit

As you'll have guessed by now: my app was using the visual studio 2010 default settings of .NET framework 4.0, compiled as x86 ... which obviously conflicts with the Microsoft.SharePoint assemblies (that are built on 3.5 and as 64bit.) :)