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

Question

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>[email protected]</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.

Solution 1

If two or more student in _xStudents.Descendants("Student") reference the same parent (by id), you then create two or more ParentDTOs with the same id, so you are then trying to insert the same Primary Key twice within your Importer class.

If you simply pre-process your _xParents, to transform them into a new list of ParentDTO, which is unique by ParentId you can then use that in your var query to get a reference to the single ParentDTO instance that refers to the given ParentId PK.

This code sample doesn't change your code much so that you can easily relate it to your original. Note, however, that you can probably optimise this, and you will also have the same problem with your SubjectDTO list as well if you are using SubjectDTO.Name to be unique (as you should be, I guess).

var parents = (from parent in _xParents.Descendants("Parent").DefaultIfEmpty()
              group parent by (String)parent.Element("ParentId") into pg
              select new ParentDTO
              {
                  ParentId = (long)pg.FirstOrDefault().Element("ParentId"),
                  Name = (String)pg.FirstOrDefault().Element("Name")
              // you might want to not use ToList here and let parents be an IEnumerable instead
              }).ToList(); 

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 parents
                    // Calling ToString each time is not fantastic
                    where (String)student.Element("Parent1") == parent.ParentId.ToString() ||
                            (String)student.Element("Parent2") == parent.ParentId.ToString() ||
                            (String)student.Element("Parent3") == parent.ParentId.ToString()

                    select parent
                ).ToList()
            };

Solution 2

The real problem is in mapping. Mapper adds that same parent two times, and hence its new entity, it is in Added state. Later dbContext treats it like new record, and tries insert. I see three options:

1. Replace StudentDTO.ParentDTO with StudentDTO.IDParentDTO
2. Add StudentDTO.IDParentDTO and ignore StudentDTO.ParentDTO in mapping
3. Play with mapping. There is a bunch of features but you just need to find them. Check this question