LightSwitch with Oracle Data Source

I have several products based on Oracle Databases with some not so easy to extend administrations. Most of those projects have to be extended with new functionalities according to the current user experience level. People want to use pads and mobile phones to run their business from anywhere.

Oracle released Oracle Data Access Components (ODAC 11.2 Release 5) with Oracle Developer Tools for Visual Studio 2012 and .NET 4.5 in middle of September last year. Oracle has partnered closely with Microsoft as part of the Visual Studio Industry Partner Program to make this early release possible. As a result Visual Studio Server Explorer now access all Oracle schema components. Integrated are also Table and Entity designers, PL/SQL editor and debugger, SQL script generator, SQL tuning advisor and source control integration. On top of all those tools Oracle integrated a Performance Analyzer. Here is a presentation video: Oracle Integration with Visual Studio 2012.

I expected with all those changes in Visual Studio 2012, to be easy to connect Oracle database and generate entity data model. But my database are different. I use identifying relationships between tables and this is not allowed in automated entity generation with object-relational mapping software.

First, what is  identifying relationships:

An identifying relationship is when the existence of a row in a child table depends on a row in a parent table. This may be confusing because it’s common practice these days to create a pseudokey for a child table, but not make the foreign key to the parent part of the child’s primary key. Formally, the “right” way to do this is to make the foreign key part of the child’s primary key. But the logical relationship is that the child cannot exist without the parent.

Example: A Person has one or more phone numbers. If they had just one phone number, we could simply store it in a column of Person. Since we want to support multiple phone numbers, we make a second table PhoneNumbers, whose primary key includes the person_id referencing the Person table.

We may think of the phone number(s) as belonging to a person, even though they are modeled as attributes of a separate table. This is a strong clue that this is an identifying relationship (even if we don’t literally include person_id in the primary key of PhoneNumbers).

On other hand a non-identifying relationship is when the primary key attributes of the parent must not become primary key attributes of the child. A good example of this is a lookup table, such as a foreign key on Person.state referencing the primary key of States.state. Person is a child table with respect to States. But a row in Person is not identified by its state attribute. I.e. state is not part of the primary key of Person.

A non-identifying relationship can be optional or mandatory, which means the foreign key column allows NULL or disallows NULL, respectively.

I wanted to keep the existing functionality of those products, but to be able to integrate them in other applications using object-relational mapping. For my luck there was a easy way to fix this by replacing the primary key with pseudo key keeping the identifying relationship as multi-column foreign key. For generating the entity model I exported the database schema into a script and replaced the multi-column foreign keys with single column foreign keys. After I imported this schema in an empty database I was able to build entity model with all relations. That model works perfectly with the original database by simply changing the connection string.

The new created entity model is no longer validating the identifying relationships, so this part have to be done manually using custom validation and query data filtering. Because otherwise the database will raise an exception and the user may not be happy about why the application mislead him to do this.

One important note here is to be carefull about the Oracle data provider used when connection to database is made. In my case I have several drives installed and some of them are old.

When I started LightSwitch and I choose to attach data source the default provider was Oracle Database (Oracle ODP.NET). First several attempts to use this one ended with no result.

Then I found the right data provider hidden under in Change Data Source dialog. Using Oracle Data Provider for .NET all entities are created correct and operational.