c# - Violation of PRIMARY KEY constraint: Cannot insert duplicate key in object -

when want persist complex model, error. think know comes from, don't know how solve it. i'm importing few feeds , create objects automatically, including children (many-to-many).

{"violation of primary key constraint 'pk_dbo.parent'. cannot insert duplicate key in object 'dbo.parent'. duplicate key value (291).\r\nthe statement has been terminated."}

the error speaks itself, how prevent it? :)

the code triggers it

var parser = new schoolfeedreader(); var update = parser.getall(); var students = mapper.map<list<studentdto>, list<student>>(update); using (var db = new schoolcontext()) {     // i'm updating every night, clean out database before import     db.database.executesqlcommand("delete student");     db.database.executesqlcommand("delete parent");     db.database.executesqlcommand("delete subject");     db.database.executesqlcommand("delete studentparent");     db.database.executesqlcommand("delete studentsubject");      students.foreach(s => db.students.add(s));     db.savechanges(); // triggers exception } 

the tl;dr

for schoolproject need import 3 xml feeds database.

• students.xml
• parents.xml
• subjects.xml

in students.xml encountered design flaw: fixed number (3) of possible parents.

<student>     <studentid>100</studentid>     <name>john doe</name>     <location>main street</location>     <parent1>1002</parent1>     <parent2>1002</parent2>     <parent3/> </student> (... more students) 

in parents.xml, things more straightforward.

<parent>     <parentid>1102</parentid>     <name>dad doe</name>     <email>dad@doe.com</email> </parent> (... more parents) 

and subjects.xml simple.

<subject>     <studentid>100</studentid>     <name>english</name> </subject> (... more subjects) 

the models

so created 3 models, including dtos.

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<parent> parents { get; set; }     public virtual icollection<subject> subjects { get; set; }  }  public class studentdto {     public long studentid { get; set; }     public string name { get; set; }     public string location { get; set; }      public list<parentdto> parents { get; set; }     public list<subjectdto> 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<student> students { get; set; }  }  public class parentdto {     public long parentid { get; set; }     public string name { get; set; }     public string email { get; set; }     public list<studentdto> students { get; set; }      public parentdto()     {         students = new list<studentdto>();     } }  public class subject {     public long subjectid { get; set; }     public string name { get; set; }     public virtual list<student> students { get; set; } }  public class subjectdto {     public string name { get; set; }     public list<studentdto> students { get; set; }      public subjectdto()     {         students = new list<studentdto>();     } } 

from xml dtos

the importer class has giant linq query need in 1 big swoop.

var query = student in _xstudents.descendants("student")             select new studentdto             {                 studentid = (long)student.element("studentid"),                 name = (string)student.element("name"),                 subjects = (                      subject in _xsubjects.descendants("subject").defaultifempty()                      (string)student.element("studentid") == (string)subject.element("studentid")                      select new subjectdto                      {                          name = (string)subject.element("name")                      }                 ).tolist(),                 parents = (                     parent in _xparents.descendants("parent").defaultifempty()                     group parent (string)parent.element("parentid") pg                     (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()             }; 

that works fine, students 2 parents, 1, data looks good.

the problem

i have these automappers in global.asax.cs:

mapper.createmap<studentdto, student>()     .formember(dto => dto.parents, opt => opt.mapfrom(x => x.parents))     .formember(dto => dto.subjects, opt => opt.mapfrom(x => x.subjects)); mapper.createmap<parentdto, parent>(); mapper.createmap<subjectdto, subject>(); 

but when start import errors on db.savechanges(). complains duplicate foreignkey on parent model. i'm thinking:

it's many-to-many relationship, if john doe's sister, jane doe, tries insert same dad doe, crashes

so how can make sure entire set of mapped business objects have 1 reference each entity; how delete duplicate daddy's , mommy's? want subject.

if 2 or more student in _xstudents.descendants("student") reference same parent (by id), create 2 or more parentdtos same id, trying insert same primary key twice within importer class.

if pre-process _xparents, transform them new list of parentdto, unique parentid can use in var query reference single parentdto instance refers given parentid pk.

this code sample doesn't change code can relate original. note, however, can optimise this, , have same problem subjectdto list if using subjectdto.name unique (as should be, guess).

var parents = (from parent in _xparents.descendants("parent").defaultifempty()               group parent (string)parent.element("parentid") pg               select new parentdto               {                   parentid = (long)pg.firstordefault().element("parentid"),                   name = (string)pg.firstordefault().element("name")               // might want not use tolist here , let parents ienumerable instead               }).tolist();   var query = student in _xstudents.descendants("student")             select new studentdto             {                 studentid = (long)student.element("studentid"),                 name = (string)student.element("name"),                 subjects = (                         subject in _xsubjects.descendants("subject").defaultifempty()                         (string)student.element("studentid") == (string)subject.element("studentid")                         select new subjectdto                         {                             name = (string)subject.element("name")                         }                 ).tolist(),                 parents = (                     parent in parents                     // calling tostring each time not fantastic                     (string)student.element("parent1") == parent.parentid.tostring() ||                             (string)student.element("parent2") == parent.parentid.tostring() ||                             (string)student.element("parent3") == parent.parentid.tostring()                      select parent                 ).tolist()             };