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

c# ef-code-first entity-framework entity-framework-6 primary-key

Question

Using Code First fluent notations, I'm attempting to preserve an object graph of POCOs that I have mapped to EF6.

But as soon as I save the object graph, I encounter exceptions for primary key violations.

The object graph is really straightforward:

One Issue can include severalWorkItems along with eachAuthor (as User ).

The items are externally populated (using a Web API)

I would anticipate that when I try to save an issue that has two workitems referencing the same author, the issue, the workitems, and one author would all be added, and the other author would either be referenced or changed.

However, this causes a primary key violation because the issue, the workitems, and both references to the same user are all put at the same time.

Simple Problem 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; }
    }
}

streamlined work item:

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;
            }
        }
    }
}

Clearly defined user object:

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

The settings of their Code-first:

    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 of this is very simple. When the database is created, the tables appear perfect, with FKs in the expected places on the columns.

Now, it would have been wonderful if the object graph had been inserted when the problem was saved, and the references to existing objects had been automatically identified and optionally inserted or referred merely.

In an effort to add relevant issues:

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 going through the object graph to manually add or attach the other items in the graph as a workaround for this problem? I would anticipate that these references would be found by defining the appropriate Foreign Key values.

1
2
11/20/2013 2:15:18 PM

Accepted Answer

This is what I ultimately did, but it was pretty laborious, and I'd still like to find a better solution. The model was found to be overly polluted by determining whether an entity was previously associated or already existed in the database (implementingIEquatable<T> is OK, however I believe incorporatingIEntityWithKey On my POCOs, the POCO is overly polluted. Tracking entities in the context did not seem to be sufficient up until that point.

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();
        }
    }
}
1
11/26/2013 8:55:50 AM

Popular Answer

The Issue object has a minor bug.

If _workItems ever became null, "return _workItems?? new List();" may always return a new WorkItem. Here is the updated document.

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

    public String Id {
        get; set;
    }

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow