Entity structure with oracle inserting parent with child

Question

We use EF 4.2 in an ASP.NET application associated with an Oracle 11 g database with an Oracle EF provider. The parent table is called CASE_PHASE and has a primary key named ID. The children's table is called CASE_STAGE and has a primary key named ID. Primary keys for both are inserted using the Before Insert trigger. This question implies that a trigger can be a problem.

This code looks like samples that I find in Julia Lerman’s book, but only inserts a new CASE_PHASE. There are no exceptions, but the child is not inserted.

//from the controllers CREATE with hard coded values for testing purposes // POST: /CasePhase/Create [HttpPost] public ActionResult Create(CASE_PHASE case_phase) { var caseStage = new CASE_STAGE { CREATED_BY_USER_ID = 1604, LAST_MODIFIED_BY_USER_ID = 1604, CURRENT = 1, STAGE_ID = 1752, DATE_CREATED = DateTime.Now, DATE_LAST_MODIFIED = DateTime.Now }; if (ModelState.IsValid) { //join new stage to phase caseStage.CASE_PHASE = case_phase; //attach linked entities to context //debugging shows case_phase has the values it needs //but caseStage does not db.CASE_PHASE.Attach(case_phase); db.CASE_PHASE.Add(case_phase); db.SaveChanges(); 

What am I missing?

Answer 1

I made sure Visual Studio has Service Pack 1 after I saw an error related to changes to StoreGeneratedPattern = "Identity" that are not being copied. All primary keys are now correctly identified in the model.

Sequences were created. After changing the StoreGeneratedPattern, I had to change the triggers so that the insert was activated when the primary key was null. It doesn't matter what value you set for the primary key in the code. If the primary key has an Identity pattern, EF will not send values ​​to the database.

It works, but it's stupid, two commits for one related transaction

  if (ModelState.IsValid) { //add the parent entity db.CASE_PHASE.Add(case_phase); db.SaveChanges(); //add new child var caseStage = new CASE_STAGE(); //...more variables initialized //and add the foreign key to the child caseStage.CASE_PHASE_ID = case_phase.ID; db.CASE_STAGE.Add(caseStage); db.SaveChanges(); return RedirectToAction........ } 

Using ADO DBContext and the beta version of the Oracle Entity Framework (I do not expect that the production version, which is currently missing, is different, because the trigger / sequence idea used by Oracle does not seem to be compatible with EF.

Alternatively, you can leave the default ID fields for StoreGeneratedPattern, and then get the primary key directly from the database using Database.SqlQuery. Add it to the parent element and add it as a foreign key for the child element.

You can choose: one selection sequence from double and one db.SaveChanges, which at least adheres to the idea of ​​an atomic transaction or two db.SaveChanges.

Answer 2

Open your .edmx file with an XML editor and find the section that starts with the following line:

 <!-- SSDL content --> 

Below should be the EntityType tag, and in it - the definition of the database table. Adding a property for the ID column contains StoreGeneratedPattern = "Identity".