Save detached object graph using Entity Framework code first causes Primary Key violation


Question

I'm trying to save an object graph of POCOs I have mapped to EF6 using Code First fluent notations.

Upon saving the object graph however, I stumble upon primary key violation exceptions.

The object graph is quite simple:

One Issue can contain multiple WorkItems with each one Author (as User).

The objects are populated externally (using a Web API)

When I attempt to save an issue with two workitems which refer to the same author, I would expect the issue to be inserted, the workitems to be inserted and one author to be inserted, and the other one to be referenced or be updated.

What happens however is that the issue is inserted, the workitems are inserted and both references to the same user are inserted, resulting in a primary key violation.

Simplified Issue object:

public class Issue
{
    public Issue()
    {
        WorkItems = new List<WorkItem>();
    }

    public string Id { get; set; }

    private List<WorkItem> _workItems;
    public List<WorkItem> WorkItems
    {
        get { return _workItems ?? new List<WorkItem>(); }
        set { _workItems = value; }
    }
}

Simplified WorkItem:

public class WorkItem
{
    public string Id { get; set; }

    public string AuthorLogin
    {
        get; set;
    }

    private WorkItemAuthor _author;
    public WorkItemAuthor Author
    {
        get { return _author; }
        set { _author = value;
            if (value != null)
            {
                AuthorLogin = value.Login;
            }
            else
            {
                AuthorLogin = string.Empty;
            }
        }
    }
}

Simplified user object:

public class User
{
    public string Login { get; set; }
    public string FullName { get; set; }
}

Their Code-first configurations:

    internal IssueConfiguration()
    {
        HasKey(x => x.Id);
        HasMany(x => x.WorkItems);
    }
    internal WorkItemConfiguration()
    {
        HasKey(x => x.Id);

        HasRequired(p => p.Author)
            .WithMany(b => b.WorkItems)
            .HasForeignKey(x=>x.AuthorLogin);
    }
    internal UsersConfiguration()
    {
        HasKey(x => x.Login);
    }

All quite straightforward. Upon database create, de tables look fine and dandy too, with FKs on the columns where one would expect them

Now when saving the issue, it would have been nice if the object graph would be inserted, and the reference to existing objects would be recognized automagically and optionally inserted or referenced only.

I attempt to add issues accordingly:

using (var db = new Cache.Context())
{
    if (db.Issues.Any(e => e.Id == issue.Id))
    {
        db.Issues.Attach(issue);
        db.Entry(issue).State = EntityState.Modified;
    }
    else
    {
        db.Issues.Add(issue);
    }
    db.SaveChanges();
}

Is the solution to this issue that I walk through the object graph to manually add or attach the other objects in the graph too? I would expect by defining the proper Foreign Key values these references would be recognized.

Accepted Answer

I finally ended up doing something similar to this, quite laborious and I would still like to find a better way. Finding out whether an entity is already attached or exists in the database turned out to be pollute the model too much (implementing IEquatable<T> is fine, but I think implementing IEntityWithKey on my POCOs pollutes the POCO too much. (and till that did not seem to suffice tracking entities in the context)

internal static void Save(this List<Issue> issues)
{
    using (var db = new Context())
    {
        foreach (var issue in issues.ToList())
        {

            foreach (var workItem in issue.WorkItems.ToList())
            {
                if (workItem.Author != null)
                {
                    var existing = db.Users.SingleOrDefault(e => e.Login == workItem.Author.Login);
                    if (existing == null)
                    {
                        db.Users.Add(workItem.Author);
                    }
                    else
                    {
                        //Update existing entities' properties
                        existing.Url = workItem.Author.Url;

                        //Replace reference
                        workItem.Author = existing;
                    }
                    db.SaveChanges();
                }

                var existingWorkItem = db.WorkItems.SingleOrDefault(e => e.Id == workItem.Id);
                if (existingWorkItem == null)
                {
                    db.WorkItems.Add(workItem);
                }
                else
                {
                    //Update existing entities' properties
                    existingWorkItem.Duration = workItem.Duration;

                    //Replace reference
                    issue.WorkItems.Remove(workItem);
                    issue.WorkItems.Add(existingWorkItem);
                }

                db.SaveChanges();
            }


            var existingIssue = db.Issues.SingleOrDefault(x => x.Id == issue.Id);
            if (existingIssue == null)
            {
                db.Issues.Add(issue);
            }
            else
            {
                //Update existing entities' properties
                existingIssue.SpentTime = issue.SpentTime;
            }

            db.SaveChanges();
        }
    }
}

Popular Answer

There is a small bug in the Issue object.

"return _workItems ?? new List();" could return a new WorkItem on every get if _workItems ever became null. Here is the fixed version.

public class Issue {
    public Issue() {
        WorkItems = new List<WorkItem>();
    }

    public String Id {
        get; set;
    }

    public List<WorkItem> WorkItems { get; private set; }
}




Licensed under: CC-BY-SA
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why