EntityFramework 4 and Oracle Foreign Key Relationship Building

Using a Model First approach to build your Entities and their Relationships and in turn, Generation Database from the Model, can have its quirks.

Here is a few procedural tips to ensure smooth sailing:

Define all Entity but do not explicitly add a property which would constitute a Foreign Key - you need to leave that up to the 'Add Association...' wizard.

So if you have an Entities/Tables:

BLOG > POST

Where A Blog can have multiple Posts and a Post can have a single Blog, then create two tables:

BLOG
BlogID (int32)
Name (string)
IsActive (boolean)

POST
PostID
Content (string)

To create the relationships between the two tables, now right click on one of the tables and choose 'Add > Association...'

You then need to configure things accordingly. In above example:

End1: BLOG
Multiplicity: 1 (One)
Navigation Property: (Checked) "Posts"

End2: BLOG
Multiplicity:* (Many)
Navigation Property: (Checked) "Blog"

Make sure you check this option: "Add foreign key properties to the 'POST' Entity"

Make the text down the bottom reads as follows:

"BLOG can have * (Many) instances of POST. Use BLOG.POSTS to access the POST instances.

POST can have 1 (One) instance of BLOG. Use POST.BLOG to access the BLOG instance."

Annoying Naming

Now when you're finished, you may notice that your POST Entity in the Data Model designer, now has a new Property called "BLOGBlog_ID".

I personally find this quite annoying, so if you just rename that scalar property, everything else will be updated (that needs to be) and you can call it something more appropriate EG "Blog_ID"


Data Type and Table Mapping Quirks

Once you have created your relationships, ensure that you right-click on an Entity in the Data Model designer and choose 'Table Mapping'.

I found the wizard would automatically map a Scalar Property that you defined as a 'string' to an Oracle datatype of 'NCLOB'.

This was happening because I did not define a 'Max Length' Attribute for the Scalar Property.

To change this: Select the Scalar Property in the Data Model Designer and hit F4 (to view its Properties window) and give it a Max Length. I found that anything up to '2000' would result in the the Oracle Mapping Type being varchar2(X) where X is the Max Length you have defined.