Generating BCS models - using picker for foreign keys in external lists

Oct 16, 2013 at 9:44 AM
Hey developers,
I have an external database and want to connect with BCS. That works fine with the generated model except foreign key references. I used the SPSF artifact "Import Model from Database as classes".

In my database model I have some tables which are responsible for "definitions". In that case foreign keys of other tables are connected within this table to define a relation between two entries to avoid a many-to-many relation.

I want to define these relations with external lists, but I do not want to define it by the ids. I want to use the Picker instead of this like here.

In my generated model I have comments like the following on each foreign key.
<!-- Warnings from SPSF: The field SomeForeignKey has a foreignkey to entity TableA, but the field is an identifier. Therefore the foreignkey relationsship could not been generated for BCS; -->
Does anyone have an idea how to resolve my problem?
Best regards,
Oct 18, 2013 at 9:39 AM
Edited Oct 18, 2013 at 10:20 AM
Hey developers,
I want to update the community. :-)

I used primary keys which consist of two foreign keys. Because of that, the automatism had a problem to generate the associations between the tables. The checkbox on "Foreign key relationships" was not activated for some tables. I tried to fix it and added manually the foreign key connections in the "Association Editor" (by clicking on an association).

That does not help me, because I could use the picker in one table for foreign keys, in another table I got a failure like:
"There is an error in the TypeDescriptors of Parameters on Method with Name 'ReadList' on Entity (External Content Type) with Name xxxxx in Namespace … The TypeDescriptors incompletely define where the Identifiers of Entity 'xxxxx' are to be read. That Entity expects exactly '2' Identifiers, but only '0' TypeDescriptors were found from which to read them."
I did not figure out what is exactly the problem but I have a good and simple workaround I think:
Use surrogate keys! (I refer to this article of Scott W. Ambler) After I refactored my database model by replacing the combined primary keys with surrogate keys and defined the two foreign keys as unique it works fantastic. Also the associations were set correctly.

Hope I can help anyone with this workaround.

Best regards,
Marked as answer by bbodensieck on 10/18/2013 at 2:39 AM
Oct 18, 2013 at 1:33 PM
Awesome Björn!

That sounds like a great workaround! Haven't had so much time recently to play with BCS, so I haven't come around this issue so far.

Thanks for updating us! :)