Microsoft Visual Studio 2012 Lightswitch release 2 with HTML5 client offers a cross browser solution for any business oriented web-application. With the ability to build fast and flexible solutions for both desktop and tablet PCs.
In one of the current projects I wanted to create a Lightswitch solution working with existing Oracle database.
First, I tried to build data source with the database service build in Lightswitch, but it doesn’t recognize the foreign keys between tables. So, I tried building a WCF RIA service creating new Web Application and adding a Telerik OpenAcces Domain Model generated from the Oracle database.
Odd, but this ended with the same result – no relations between entity objects.
It all appear for third time when I tried with ADO.NET Entity Data Model, but this time there was one relation.
I spend some time checking into database structure and I found logical reason for all this. All foreign keys in this database are referencing two or more columns (except the one I found in ADO.NET Entity Data Model). It’s not possible for Entity model to create an relations between two objects based on two properties at the same time. This is why the only possible scenario for Data Entity Model builders is to skip those foreign keys.
From other point of view this type of foreign keys is the best solution for this database structure. This way the content of each table is validated by business logic stored in several different tables. In other words, when creating a new record in table “A” you can reference a record in table “B” only in case table “A” and table “B” have the same value in the column referencing table “C”.
How to build the Data Entity Model?
I’m not familiar with the option to alter the model building add-on in order to operate with this type foreign keys. Also, this can’t be a real solution for any future projects. So, the solution shall be connected to a change in database.
Can’t change the database structure 🙂
There one fast way… I exported all user objects into a database build script and with several replace requests I altered all foreign key creations to reference only one column – the column that is different in each foreign key declaration, not those that are constantly repeated for creating the business logic.
This forced me to remove some indexes and some unique keys because they are now duplicated by primary keys definitions. After this I used an Oracle Express database to build the schema and even without any data in it I was able to create the ADO.NET Entity Model with all needed foreign keys.
With this fixed and empty database schema the Telerik OpenAcces Domain Model generator and the Lightswitch direct database access will also work, but I choose the make a WCF RIA service based on ADO.NET Entity Data model to separate the Lightswitch from Oracle with a layer where I can build in case it’s needed.
After this the Lightswitch application building went smooth.