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).
{"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."}
The error speaks for itself, but how to prevent it? :)
The code that 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, 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 => db.Students.Add(s));
db.SaveChanges(); // Triggers the Exception
}
The TL;DR
For a schoolproject I need to import 3 XML Feeds into the database.
- Students.xml
- Parents.xml
- Subjects.xml
In Students.xml I encountered a design flaw: a 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 are more straightforward.
<parent>
<ParentId>1102</ParentId>
<Name>Dad Doe</Name>
<Email>dad@doe.com</Email>
</parent>
(... more parents)
And Subjects.xml is also very simple.
<subject>
<StudentId>100</StudentId>
<Name>English</Name>
</subject>
(... more subjects)
The Models
So I created 3 models, including the 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 to DTOs
The Importer class has this giant LINQ query to get everything I need in one big swoop.
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()
};
That works fine, some students get 2 parents, some get 1, so my data looks good.
The Problem
I have these AutoMappers in my 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 I start the import I get errors on my db.SaveChanges(). It complains about a duplicate ForeignKey on the Parent model. So I'm thinking:
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
So How can I make sure that the entire set of Mapped Business Objects only have 1 reference to each entity; how to delete the duplicate daddy's and mommy's? I probably want to do this also for Subject.
