I am currently developing a solution that requires me to integrate a SharePoint 2010 list (generic list type) with items from a SQL Server database. In addition to this hypothetical field being surfaced in SharePoint as an External List, I have created an ASP.NET MVC Web API that also accesses the same table.
What are my options for reconciling the fact that I cannot have multiple values in an external data column? I could create another list that contains the multiple values and an ID to the original list to simulate a one-to-many relation ship and display that using CSOM in the UI or similar. But I'm not sure that would be the easiest or most transactionally secure method. method.