I have been struggling with this project for about two weeks now. I have successfully created an External Content Type using Visual Studio and have deployed it. I was also able to use the SharePoint UI to play around with External Lists and External Data Web Part (or whatever it is called).
However, I have run into several problems trying to take the next step.
Any columns that are GUID are not seen using the External List. (They are seen using the Web Part but far as I know that only provides a read-only list...no create and update capabilities.) While I am ok with hiding the primary key guid of the item in question there are other columns that are foreign keys to other tables that need to be set. Is there any way to get the External List to process guid columns or do I have to resort to custom pages and/or custom web parts? (Or is there another solution...?)
Choice columns. How can I get a given column in my main item to be presented as a list of dropdown choices with the values coming from the database? I think I read that after I use the SharePoint UI to create the External List I can use Designer to modify the forms. Can I use Visual Studio to deploy the creation of the list and its associated pages? Any links to how to's would be helpful. Is this related to Associations?
Entity Framework and Navigation properties. Navigation properties in Entity Framework map to objects instead of id's or keys. So given the following tables
Order
OrderId OrderDate StatusKey
Status
StatusKey StatusLabel
maps to the following objects
Order
-----------------
OrderId
OrderDate
Status ---> Status
Status
-----------------
StatusKey
StatusLabel
My question is how does this map out correctly with External Types and Type Descriptors? I am pretty sure I need to eager load the navigation properties. Am I putting in Status as the data type or do I need to flatten out the EF model for the External Type? (This same object model relates to my second question regarding choice columns.)
Thank you for any insight you can provide.