Databases
StackOverflow2013
Postsdbo
POOne-to-One or One-to-None Entity Framework Referential Constraint Violation
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOne-to-One or One-to-None Entity Framework Referential Constraint Violation
    primarykey
    data
    text
    <p>Why does Microsoft's Entity Framework fail for a fundamentally simple single foreign key, two table <strong>Name</strong> and <strong>Email</strong> in one form fail... and how do I fix it? </p> <p>We've all heard of Frequent Flyer Programs. Join my Frequent Failure Program, inspired by Edison, to learn how not to use EF. Check out the code I tried and the corresponding errors below.</p> <p><a href="http://weblogs.asp.net/manavi/about.aspx" rel="nofollow noreferrer">Morteza Manavi</a> describes this <a href="http://weblogs.asp.net/manavi/archive/2011/04/14/associations-in-ef-4-1-code-first-part-3-shared-primary-key-associations.aspx" rel="nofollow noreferrer">One-to-One Primary Key Association or Shared Primary Key</a> problem. Telerik's <strong>OpenAccess</strong> ORM calls this "<a href="http://www.telerik.com/help/openaccess-orm/openaccess-tasks-define-model-inheritance-vertical-mapping.html" rel="nofollow noreferrer">Vertical Inheritanc</a>e". EF doesn't have a consistent term or property like Ruby On Rails' <strong>ActiveRecord</strong> <a href="http://guides.rubyonrails.org/association_basics.html" rel="nofollow noreferrer">belongs_to</a>. </p> <p>The T-SQL script to build the two tables, foreign key, and unique constraints shown in this SQL Server diagram are included below if you want to quickly prototype and play...</p> <p><img src="https://i.stack.imgur.com/M330A.png" alt="2 Tables, 4 Fields Total"></p> <h2>Pedagogical Use Case</h2> <p>There is NO many in this scenario. If you're looking for 1-to-many or many-to-many answers, sorry, you're in the wrong place. Multiplicity is 1 Person to 0..1 Email. </p> <p>If the user leaves Email blank in the Razor form, the relationship between Person and ContactInfoes should be 1:0 (i.e., one-to-none [12n1]) with no ContactInfoes record.</p> <p><img src="https://i.stack.imgur.com/tcXPi.png" alt="2 Entities, 0..1 Multiplicity"> Otherwise ContactInfoes has a 1:1, (i.e., one-to-one [121]) relationship with People. Or, the People record has a 1:1 relationship with the ContactInfoes record. </p> <p>I've done squeal [SQL] for years and like pictures, but the above EDMX screen shot doesn't provide the complete picture of what's going on in the background. In order to have the one-to-none-or-one EF and <em>Code Firsterers</em> increase the complexity by creating a virtual <em>ICollection</em> for each entity referencing the other entity as listed in the DbContext Code First model below.</p> <pre><code>public class MVC3EF4Context : System.Data.Entity.DbContext { public System.Data.Entity.DbSet&lt;Person&gt; People { get; set; } public System.Data.Entity.DbSet&lt;ContactInfo&gt; ContactInfoes { get; set; } } public class Person { [Key] public int PersonId { get; set; } [Required] public string Name { get; set; } public virtual ICollection&lt;ContactInfoes&gt; ContactInfo { get; set; } } public class ContactInfo { [Key] [ForeignKey("PersonId")] public int PersonId { get; set; } [Required] public string Email { get; set; } public virtual ICollection&lt;People&gt; Person { get; set; } } </code></pre> <p>The advantage of this additional complexity is IntelliSense in the strongly-typed controller and views the code generator creates from the People model --which can come from an EDMX diagram, T4 .tt files, or Code First classes. </p> <p>The intuitive thing to do with EF for a Noob is to simply add <code>@Html.EditorFor(model =&gt; model.**ContactInfoes.Email**)</code> to the Create.cshtml and Edit.cshtml automatically generated Razor forms for a person.</p> <p><img src="https://i.stack.imgur.com/sHrtb.jpg" alt="Person Form with Associated ContactInfoes.Email input field"></p> <h2>Fail Fail Fail</h2> <p>Although an insert into the ContactInfoes table automagically works great when there is an Email because EF builds a full graph of entities :) , unfortunately, </p> <p><code>_Email = StructuralObject.SetValidValue(value, false);</code> </p> <p>fails on insert for no Email because <code>IsNullable=false</code> . The auto code generation is smart enough to detect and process descendant entities, but not read my mind as a developer and simply skip creating the entity when there is no Email. If there is a "if there is an error, that's OK, build all the other entities in the graph, but skip this one" command I don't know what it is. As a developer I have to intercept and interject when no Email is entered.</p> <p>Unfortunately, the automatically generated <code>db.People.Attach(people);</code> also fails for updates (even though the predicate ModelState.IsValid is true). EF apparently doesn't check for its descendant relationships when a form is submitted --or if it does, I don't know how to clue it in.</p> <pre><code>A referential integrity constraint violation occurred: The property values that define the referential constraints are not consistent between principal and dependent objects in the relationship. </code></pre> <p><a href="https://stackoverflow.com/questions/7265120/mvc-3-with-entity-framework-and-editing-multiple-objects-leads-to-referential-i">MVC 3 with Entity Framework and Editing Multiple Objects leads to &quot;referential integrity constraint violation&quot;</a> suggests using AutoMapper to fix this problem, a bit over the top for 3-properties total in the entire solution. Bengt Berge's <a href="http://bengtbe.com/blog/2009/04/14/using-automapper-to-map-view-models-in-asp-net-mvc/" rel="nofollow noreferrer">Intro to AutoMapper</a> is quick and simple. Suggestions to use FormsCollection don't seem apropos either since there isn't any additional info that isn't already in the People and ContactInfoes entities.</p> <hr> <p>So, the automatically generated code (with my attempt at a modicum of error reporting / exception handler using try and catch), shown here, somehow needs to make decisions on whether to create or delete an InfoContact entity. </p> <pre><code>[HttpPost] public ActionResult Edit(People people) { if (ModelState.IsValid) { try { db.People.Attach(people); } catch (Exception ex) { return Content("&lt;h1&gt;Error!&lt;/h1&gt;" + ex.Source + "&lt;br /&gt;\n\r" + ex.Message + "&lt;br /&gt;\n\r" + ex.InnerException + "&lt;br /&gt;\n\r" + ex.StackTrace+ "&lt;br /&gt;\n\r" + ex.TargetSite + "&lt;br /&gt;\n\r" + ex.HelpLink + "&lt;br /&gt;\n\r" + ex.Data + "&lt;br /&gt;\n\r" + "If this is an emergency call 911.\n\r" ); } db.ObjectStateManager.ChangeObjectState(people, EntityState.Modified); db.SaveChanges(); return RedirectToAction("Index"); } ViewBag.PersonId = new SelectList(db.ContactInfoes, "PersonId", "Email", people.PersonId); return View(people); } </code></pre> <p>A few Side note tangents</p> <ol> <li><p>Although this is a vertical inheritance relationship Microsoft's code generator produces <code>ViewBag.PersonId = new SelectList(db.ContactInfoes, "PersonId", "Email", people.PersonId);</code> Why would anyone use a drop down list in the self-referential scenario clearly defined in this model? Sure, the programmer can just delete the auto-code, or ignore it, but why generate it in the first place when there's no need? </p></li> <li><p>Is there a better way to debug than my crude exception handler implementation? The output provides the square root of squat, i.e. nothing useful. I'm assuming Log4net and ELMAH won't give more info, just track what is available.</p></li> <li><p>Is there really an advantage of using EF after you get done finally doing all the ViewModel mapping?</p></li> </ol> <h2>ViewModel</h2> <p>It turns out the secret sauce for using IntelliSense in strongly typed form processing is AutoMapper related, in that the developer needs to add a ViewModel and map between it and the People and ContactInfoes entities, hence side note tangent #3.</p> <pre><code>using System.ComponentModel.DataAnnotations; public class PCI //PersonContactInfo { [Key] public int PersonId { get; set; } [Required] public string Name { get; set; } // Add Annotation for valid Email or Null public string Email { get; set; } } </code></pre> <h2>View Modifications to Use the ViewModel</h2> <p>Using the ViewModel requires only two changes in Edit and Create cshtml Razor form files.</p> <p>The first line goes from <code>@model MVC3EF4.Models.People</code> to <code>@model MVC3EF4.Models.PCI</code></p> <p>and my original modification </p> <p><code>@Html.EditorFor(model =&gt; model.ContactInfoes.Email)</code></p> <p>becomes</p> <p><code>@Html.EditorFor(model =&gt; model.Email)</code></p> <p>Note: I didn't start out building my new Controller and Razor Forms using the PCI ViewModel.<br> I'm also not using the DbContext, but the EDMX code generation = default. If you want to use DbContext set the EDMX code generation property = none.</p> <h2>Controller Modifications</h2> <pre><code>[HttpPost] public ActionResult Create(PCI pci) //use the ViewModel PCI instead of People { if (ModelState.IsValid) { // THIS IS WHERE AutoMapper WOULD BE HANDY FOR MAPPING ViewModel // to the People and ContactInfoes Models // Map PCI to People People people = new People(); people.PersonId = pci.PersonId; people.Name = pci.Name; // Map PCI to ContactInfoes --if there is an Email if (pci.Email != null &amp;&amp; pci.Email.Length &gt; 3) { // KNOWN AND THROWN ???? // Why is there no concurrency error thrown? // How is this added when PersonId isn't known? // This isn't standard functional programming. This is how EF builds a graph. db.AddToContactInfoes( new ContactInfoes { PersonId = people.PersonId, Email = pci.Email } ); } // else don't add ContactInfoes entity/table. db.People.AddObject(people); db.SaveChanges(); return RedirectToAction("Index"); } // Where does a dropdownlist get used? It doesn't for a 1:0..1 relationship. //ViewBag.PersonId = new SelectList(db.ContactInfoes //, "PersonId", "Email", people.PersonId); return View(pci); } </code></pre> <h2>Edit</h2> <pre><code> // GET: /PersonContactInfo/Edit/5 public ActionResult Edit(int id) { People people = db.People.Single(p =&gt; p.PersonId == id); // Map People to ViewModel PCI... PCI pci = new PCI() { PersonId = people.PersonId, Name = people.Name }; if (people.ContactInfoes != null) { pci.Email = people.ContactInfoes.Email; } /* why a SelectList in a one-to-one or one-to-none? * what is to select? * ViewBag.PersonId = new SelectList(db.ContactInfoes , "PersonId" , "Email" , people.PersonId); */ return View(pci); } </code></pre> <h2>Edit Post</h2> <pre><code> // // POST: /PersonContactInfo/Edit/5 [HttpPost] // THIS DOESN'T WORK //public ActionResult Edit(People people) //use the ViewModel instead //public ActionResult Edit(FormCollection col) //No need, everything is available from strongly typed ViewModel public ActionResult Edit(PCI pci) { if (ModelState.IsValid) { // THIS DOESN'T WORK // var people = new People(); --reload what the Person was from the database People people = db.People.Single(p =&gt; p.PersonId == pci.PersonId); try { people.Name = pci.Name; if (pci.Email != null &amp;&amp; pci.Email.Length &gt; 3) { if (people.ContactInfoes == null) { var ci = new ContactInfoes { PersonId = pci.PersonId, Email = pci.Email }; db.AddToContactInfoes(ci); // THIS DOESN'T WORK //db.ContactInfoes.Attach(ci) // this causes an error on the next line // A referential integrity constraint violation occurred: A primary key property that is a part of referential integrity constraint cannot be changed when the dependent object is Unchanged unless it is being set to the association's principal object. The principal object must be tracked and not marked for deletion. // people.ContactInfoes = ci; // THIS DOESN'T WORK //people.ContactInfoesReference.Attach(new ContactInfoes { PersonId = pci.PersonId, Email = pci.Email }); //Attach is not a valid operation when the source object associated with this related end is in an added, deleted, or detached state. Objects loaded using the NoTracking merge option are always detached. } else people.ContactInfoes.Email = pci.Email; } else // no user input for Email from form so there should be no entity { // THIS DOESN'T WORK // people.ContactInfoes = null; // people.ContactInfoesReference = null; ContactInfoes ci = people.ContactInfoes; if (ci != null) //if there isn't an ContactInfo record, trying to delete one will cause an error. db.ContactInfoes.DeleteObject(ci); /* * THIS DOESN'T WORK // this causes a concurrency error var ci = new ContactInfoes(); ci.PersonId = pci.PersonId; db.AttachTo("ContactInfoes", ci); db.ContactInfoes.DeleteObject(ci); */ } // THIS DOESN'T WORK // db.People.Attach(people); // doing People people = db.People.Single(p =&gt; p.PersonId == pci.PersonId); makes people automatically attached // The object cannot be attached because it is already in the object context. An object can only be reattached when it is in an unchanged state. } catch (Exception ex) { return Content("&lt;h1&gt;Error!&lt;/h1&gt;" + ex.Source + "&lt;br /&gt;\n\r" + ex.Message + "&lt;br /&gt;\n\r" + ex.InnerException + "&lt;br /&gt;\n\r" + ex.StackTrace+ "&lt;br /&gt;\n\r" + ex.TargetSite + "&lt;br /&gt;\n\r" + ex.HelpLink + "&lt;br /&gt;\n\r" + ex.Data + "&lt;br /&gt;\n\r" ); } db.ObjectStateManager.ChangeObjectState(people, EntityState.Modified); db.SaveChanges(); return RedirectToAction("Index"); } //ViewBag.PersonId = new SelectList(db.ContactInfoes, "PersonId", "Email", people.PersonId); return View(pci);} </code></pre> <hr> <h2>SQUEAL [SQL] Data Definition Language</h2> <pre><code>-- -------------------------------------------------- -- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure -- -------------------------------------------------- -- Date Created: 05/31/2012 18:03:38 -- Generated from EDMX file: C:\Users\Jb\Documents\Visual Studio 11\Projects\MVC3EF4\MVC3EF4\Models\PersonContactInfoModel.edmx -- -------------------------------------------------- SET QUOTED_IDENTIFIER OFF; GO USE [MVC3EF4]; GO IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]'); GO -- -------------------------------------------------- -- Dropping existing FOREIGN KEY constraints -- -------------------------------------------------- IF OBJECT_ID(N'[dbo].[FK_PersonContactInfo]', 'F') IS NOT NULL ALTER TABLE [dbo].[ContactInfoes] DROP CONSTRAINT [FK_PersonContactInfo]; GO -- -------------------------------------------------- -- Dropping existing tables -- -------------------------------------------------- IF OBJECT_ID(N'[dbo].[ContactInfoes]', 'U') IS NOT NULL DROP TABLE [dbo].[ContactInfoes]; GO IF OBJECT_ID(N'[dbo].[People]', 'U') IS NOT NULL DROP TABLE [dbo].[People]; GO -- -------------------------------------------------- -- Creating all tables -- -------------------------------------------------- -- Creating table 'ContactInfoes' CREATE TABLE [dbo].[ContactInfoes] ( [PersonId] int NOT NULL, [Email] nvarchar(120) NOT NULL ); GO -- Creating table 'People' CREATE TABLE [dbo].[People] ( [PersonId] int IDENTITY(1,1) NOT NULL, [Name] nvarchar(50) NOT NULL ); GO -- -------------------------------------------------- -- Creating all PRIMARY KEY constraints -- -------------------------------------------------- -- Creating primary key on [PersonId] in table 'ContactInfoes' ALTER TABLE [dbo].[ContactInfoes] ADD CONSTRAINT [PK_ContactInfoes] PRIMARY KEY CLUSTERED ([PersonId] ASC); GO -- Creating primary key on [PersonId] in table 'People' ALTER TABLE [dbo].[People] ADD CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED ([PersonId] ASC); GO -- -------------------------------------------------- -- Creating all FOREIGN KEY constraints -- -------------------------------------------------- -- Creating foreign key on [PersonId] in table 'ContactInfoes' ALTER TABLE [dbo].[ContactInfoes] ADD CONSTRAINT [FK_PersonContactInfo] FOREIGN KEY ([PersonId]) REFERENCES [dbo].[People] ([PersonId]) ON DELETE CASCADE ON UPDATE NO ACTION; GO -- -------------------------------------------------- -- Creating FOREIGN KEY Relationship Documentation -- -------------------------------------------------- EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'One-to-One or One-to-None' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ContactInfoes', @level2type=N'CONSTRAINT',@level2name=N'FK_PersonContactInfo' GO -- -------------------------------------------------- -- Script has ended -- -------------------------------------------------- </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USJules Bartow
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POOne-to-One or One-to-None Entity Framework Referential Constraint Violation
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POOne-to-One or One-to-None Entity Framework Referential Constraint Violation
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhere do the two collections in the model classes come from? This is surely wrong for a one-to-one relationship. You mentioned "Code First" but then you talk that something "created" the collections. But the only one who creates collections in model classes are *you* if you are using Code First approach. Are the classes actually created by Database-First or Model-First using the DbContext T4 template?
      singulars
      1. POOne-to-One or One-to-None Entity Framework Referential Constraint Violation
      1. USSlauma
    2. COAnd why is there suddenly an `ObjectStateManager` and an `AddObject` method? In the beginning you showed a `DbContext` but now it's apparently an `ObjectContext`.
      singulars
      1. POOne-to-One or One-to-None Entity Framework Referential Constraint Violation
      1. USSlauma
    3. COSlauma, You're prejudiced. I go both ways because the F in EF stands for Fruit: apples (DbContext), oranges (ObjectContext), and pears (T4). Nonetheless, you are correct. Mixing apples and oranges with the same entity names at the same time won't compile, throwing an "object is declared twice" error. However, 1:0..1 EF problem, to be an entity or not to be an entity, should be similar within a single context. I've been excluding one context or the other from the project and modifying the code slightly (Add vs. AddTo, ...) depending on the context. Used EDMX default code gen vs. T4.
      singulars
      1. POOne-to-One or One-to-None Entity Framework Referential Constraint Violation
      1. USJules Bartow
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload