title: Violation of PRIMARY KEY constraint: Cannot insert duplicate key in object
body: <p>When I want to persist a complex model, I get this error. I think I know where it comes from, but I don't know how to solve it. I'm importing a few feeds and create objects automatically, including children (many-to-many).</p>
  
  <blockquote>
    <p>{"Violation of PRIMARY KEY constraint 'PK_dbo.Parent'. Cannot insert
    duplicate key in object 'dbo.Parent'. The duplicate key value is
    (291).\r\nThe statement has been terminated."}</p>
  </blockquote>
  
  <p>The error speaks for itself, but how to prevent it? :)</p>
  
  <p><strong>The code that triggers it</strong></p>
  
  <pre><code>var parser = new SchoolFeedReader();
  var update = parser.GetAll();
  var students = Mapper.Map&lt;List&lt;StudentDTO&gt;, List&lt;Student&gt;&gt;(update);
  using (var db = new SchoolContext())
  {
      // I'm updating every night, so clean out the database before import
      db.Database.ExecuteSqlCommand("DELETE FROM Student");
      db.Database.ExecuteSqlCommand("DELETE FROM Parent");
      db.Database.ExecuteSqlCommand("DELETE FROM Subject");
      db.Database.ExecuteSqlCommand("DELETE FROM StudentParent");
      db.Database.ExecuteSqlCommand("DELETE FROM StudentSubject");
  
      students.ForEach(s =&gt; db.Students.Add(s));
      db.SaveChanges(); // Triggers the Exception
  }
  </code></pre>
  
  <p><strong>The TL;DR</strong></p>
  
  <p>For a schoolproject I need to import 3 XML Feeds into the database.</p>
  
  <ul>
  <li>Students.xml</li>
  <li>Parents.xml</li>
  <li>Subjects.xml</li>
  </ul>
  
  <p>In Students.xml I encountered a design flaw: a fixed number (3) of possible Parents.</p>
  
  <pre><code>&lt;student&gt;
      &lt;StudentId&gt;100&lt;/StudentId&gt;
      &lt;Name&gt;John Doe&lt;/Name&gt;
      &lt;Location&gt;Main Street&lt;/Location&gt;
      &lt;Parent1&gt;1002&lt;/Parent1&gt;
      &lt;Parent2&gt;1002&lt;/Parent2&gt;
      &lt;Parent3/&gt;
  &lt;/student&gt;
  (... more students)
  </code></pre>
  
  <p>In Parents.xml, things are more straightforward.</p>
  
  <pre><code>&lt;parent&gt;
      &lt;ParentId&gt;1102&lt;/ParentId&gt;
      &lt;Name&gt;Dad Doe&lt;/Name&gt;
      &lt;Email&gt;dad@doe.com&lt;/Email&gt;
  &lt;/parent&gt;
  (... more parents)
  </code></pre>
  
  <p>And Subjects.xml is also very simple.</p>
  
  <pre><code>&lt;subject&gt;
      &lt;StudentId&gt;100&lt;/StudentId&gt;
      &lt;Name&gt;English&lt;/Name&gt;
  &lt;/subject&gt;
  (... more subjects)
  </code></pre>
  
  <p><strong>The Models</strong></p>
  
  <p>So I created 3 models, including the DTOs.</p>
  
  <pre><code>public class Student
  {
      [DatabaseGenerated(DatabaseGeneratedOption.None)]
      public long StudentId { get; set; }
      public string Name { get; set; }
      public string Location { get; set; }
  
      [InverseProperty("Students")]
      public virtual ICollection&lt;Parent&gt; Parents { get; set; }
      public virtual ICollection&lt;Subject&gt; Subjects { get; set; } 
  }
  
  public class StudentDTO
  {
      public long StudentId { get; set; }
      public string Name { get; set; }
      public string Location { get; set; }
  
      public List&lt;ParentDTO&gt; Parents { get; set; }
      public List&lt;SubjectDTO&gt; Subjects { get; set; } 
  }
  
  public class Parent
  {
      [DatabaseGenerated(DatabaseGeneratedOption.None)]
      public long ParentId { get; set; }
      public string Name { get; set; }
      public string Email { get; set; }
  
      [InverseProperty("Parents")]
      public virtual ICollection&lt;Student&gt; Students { get; set; } 
  }
  
  public class ParentDTO
  {
      public long ParentId { get; set; }
      public string Name { get; set; }
      public string Email { get; set; }
      public List&lt;StudentDTO&gt; Students { get; set; }
  
      public ParentDTO()
      {
          Students = new List&lt;StudentDTO&gt;();
      }
  }
  
  public class Subject
  {
      public long SubjectId { get; set; }
      public string Name { get; set; }
      public virtual List&lt;Student&gt; Students { get; set; }
  }
  
  public class SubjectDTO
  {
      public string Name { get; set; }
      public List&lt;StudentDTO&gt; Students { get; set; }
  
      public SubjectDTO()
      {
          Students = new List&lt;StudentDTO&gt;();
      }
  }
  </code></pre>
  
  <p><strong>From XML to DTOs</strong></p>
  
  <p>The Importer class has this giant LINQ query to get everything I need in one big swoop.</p>
  
  <pre><code>var query = from student in _xStudents.Descendants("Student")
              select new StudentDTO
              {
                  StudentId = (long)student.Element("StudentId"),
                  Name = (String)student.Element("Name"),
                  Subjects = (
                       from subject in _xSubjects.Descendants("Subject").DefaultIfEmpty()
                       where (String)student.Element("StudentId") == (String)subject.Element("StudentId")
                       select new SubjectDTO
                       {
                           Name = (String)subject.Element("Name")
                       }
                  ).ToList(),
                  Parents = (
                      from parent in _xParents.Descendants("Parent").DefaultIfEmpty()
                      group parent by (String)parent.Element("ParentId") into pg
                      where (String)student.Element("Parent1") == (String)pg.FirstOrDefault().Element("ParentId") ||
                            (String)student.Element("Parent2") == (String)pg.FirstOrDefault().Element("ParentId") ||
                            (String)student.Element("Parent3") == (String)pg.FirstOrDefault().Element("ParentId")
  
                      select new ParentDTO
                      {
                          ParentId = (long)pg.FirstOrDefault().Element("ParentId"),
                          Name = (String)pg.FirstOrDefault().Element("Name")
                      }
                  ).ToList()
              };
  </code></pre>
  
  <p>That works fine, some students get 2 parents, some get 1, so my data looks good.</p>
  
  <p><strong>The Problem</strong></p>
  
  <p>I have these AutoMappers in my Global.asax.cs:</p>
  
  <pre><code>Mapper.CreateMap&lt;StudentDTO, Student&gt;()
      .ForMember(dto =&gt; dto.Parents, opt =&gt; opt.MapFrom(x =&gt; x.Parents))
      .ForMember(dto =&gt; dto.Subjects, opt =&gt; opt.MapFrom(x =&gt; x.Subjects));
  Mapper.CreateMap&lt;ParentDTO, Parent&gt;();
  Mapper.CreateMap&lt;SubjectDTO, Subject&gt;();
  </code></pre>
  
  <p>But when I start the import I get errors on my <code>db.SaveChanges()</code>. It complains about a duplicate ForeignKey on the Parent model. So I'm thinking: </p>
  
  <blockquote>
    <p><em>it's a Many-to-Many relationship, so if John Doe's sister, Jane Doe, tries to insert the same Dad Doe, then it crashes</em></p>
  </blockquote>
  
  <p>So How can I make sure that the entire set of Mapped Business Objects only have 1 reference to each entity; how to <strong>delete the duplicate daddy's and mommy's</strong>? I probably want to do this also for Subject.</p>