Entity Framework creating duplicate objects from list when object has composite Primary Key

Question

I have an object with a child collection as such:

public class ClaimGroup : BaseModel
{        
    public int Id { get; set; }

    [Required,StringLength(100)]
    public string Name { get; set; }

    public int Order { get; set; }

    public bool Include { get; set; }

    public virtual ICollection<ClaimGroupItem> Items { get; set; }
}

The ClaimGroupItem is:

public class ClaimGroupItem : BaseModel
{
    [Key,Column(Order = 0),DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int ClaimGroupId { get; set; }
    [ForeignKey("ClaimGroupId")]
    public virtual ClaimGroup ClaimGroup { get; set; }

    [Key,Column(Order = 1),DatabaseGenerated(DatabaseGeneratedOption.None)]        
    public int MenuItemId { get; set; }
    [ForeignKey("MenuItemId")]
    public virtual MenuItem MenuItem { get; set; }

    public string ClaimValue { get; set; }
}

As you can see, it has a composite primary key: MenuItemId and ClaimGroupId.

On updating, it creates duplicates of the ClaimGroupItem objects, with POCO objects being set correctly, but then it creates dynamic proxy items with the same values, hence duplicating the objects.

E.g.:

var items = viewModel.Items.Where(c => !string.IsNullOrEmpty(c.ClaimValue));

The items collection above containts 10 ClaimGroupItemViewModel objects as shown in the image below.

enter image description here

However, when I map the viewModel objects to the Model objects, the collection then has 20 objects, 10 of which are proxy items as seen below:

itemToSave.Items = (from i in items
                    select new ClaimGroupItem
                    {
                        ClaimValue = i.ClaimValue,
                        MenuItemId = i.MenuItemId,
                    })
                    .ToList();

enter image description here

Then when the object goes to save, I get the following error:

 _repository.Update<ClaimGroup>(itemToSave);

Violation of PRIMARY KEY constraint 'PK_dbo.ClaimGroupItems'. Cannot insert duplicate key in object 'dbo.ClaimGroupItems'. The duplicate key value is (20, 6). The statement has been terminated.

The error makes sense, EF is trying to save 10 duplicate objects. Why is Entity Framework creating 10 new objects and hence duplicates?

Here is the code on POST that gets the whole list of 79 items in viewModel.Items, then we select just the ones with claimvalue not null. There are NO duplicates at this stage.

[HttpPost, ValidateAntiForgeryToken]
public ActionResult Group([Bind(Include = "Id,Name,Order,Include,Items")] ClaimGroupViewModel viewModel)
{
    if (ModelState.IsValid)
    {
        ClaimGroup itemToSave = _repository.Get<ClaimGroup>(viewModel.Id);
        itemToSave.Include = viewModel.Include;
        itemToSave.Name = viewModel.Name;
        itemToSave.Order = viewModel.Order;
        var items = viewModel.Items.Where(c => !string.IsNullOrEmpty(c.ClaimValue));

        // There is just 10 items in items variable at this point

         itemToSave.Items = (from i in items
                            select new ClaimGroupItem
                            {
                                ClaimValue = i.ClaimValue,                                
                                MenuItem = new MenuItem { Id = i.MenuItemId}
                            })
                            .ToList();

        _repository.Update<ClaimGroup>(itemToSave);
        return RedirectToAction("Groups", new { updated = true });
        }

    return View(viewModel);
    }

enter image description here

But why it shouldn't? ;) EF is the abstraction between DB and your C# code. If it does exactly the same that DB does, then why you need a DB? ) — Maris, Apr 14 '15 at 05:00
Is `ClaimGroupItem.ClaimGroupId` intended to be the `ClaimGroup.Id` ? — Alex, Apr 14 '15 at 05:06
I don't understand what you mean Maris. @Alex, yes that is correct. — Rhys Stephens, Apr 14 '15 at 05:42
Then it should normally be a foreign key, not part of the `ClaimGroupItem` primary key. — Alex, Apr 14 '15 at 05:51
I did attempt adding a new primary key to ClaimGroupItem and removing the composite primary key, but I still had exactly the same issue. Why would EF created duplicate rows though? — Rhys Stephens, Apr 14 '15 at 05:58
Ah I see it now I think. Shall I attempt to formulate an answer? — Alex, Apr 14 '15 at 06:02
Yes please. It's driving me crazy. I'm sure that it used to work fine, which is the really annoying thing. — Rhys Stephens, Apr 14 '15 at 06:13
Where does `itemToSave` come from? It looks like it loads existing `Items` from the database in debug view after you added the other items. You can confirm that by monitoring executed SQL. — Gert Arnold, Apr 14 '15 at 07:25
That lazy loads the existing ClaimGroupItems before we POST the new values to replace them. So I see now that itemToSave.Items = (from i etc) is not replacing the items, rather appending to the list. Why would it do this? — Rhys Stephens, Apr 14 '15 at 07:39
I'm pretty sure it loads them again after you replace the collection. You shouldn't replace the collection, but `Add()` items to it. — Gert Arnold, Apr 14 '15 at 07:50
I don't know. Some people initialize a collection property by assigning an empty list to it, and then expect EF to load it... So many use cases. But I assume your comment means that this was what happened in your case? — Gert Arnold, Apr 15 '15 at 09:25

score 0 · Answer 1 · answered Apr 15 '15 at 07:30

0

If you don't need Proxies, during construction of your DBContext set ProxyCreationEnabled = false and check. I have seen cases when we don't even need Proxies and EF by default creates one for all Entities.

answered Apr 15 '15 at 07:30

Nirmal Thakur

182
2
10

I do need them generally, just not when I'm reassigning a list. – Rhys Stephens Apr 16 '15 at 00:36
Glad to know that you got this working. I too have been having problems with these proxies but I agree with you, we do need them generally. – Nirmal Thakur Apr 16 '15 at 03:11

score 0 · Accepted Answer · answered Apr 16 '15 at 01:34

I finally got it working. It was as simple as calling the

itemToSave.Items.Clear()

method to make sure it doesn't load the old proxy objects. What a pain that was to figure out! Here is my working code. Thanks for everyone's help.

[HttpPost, ValidateAntiForgeryToken]
public ActionResult Group([Bind(Include = "Id,Name,Order,Include,Items")] ClaimGroupViewModel viewModel)
{
    if (ModelState.IsValid)
    {
        ClaimGroup itemToSave = _repository.Get<ClaimGroup>(viewModel.Id);
        itemToSave.Include = viewModel.Include;
        itemToSave.Name = viewModel.Name;
        itemToSave.Order = viewModel.Order;
        itemToSave.Items.Clear();// This needs to be done, otherwise it will try and load the list from the DB again.                
        itemToSave.Items = (from i in viewModel.Items
                            where !string.IsNullOrEmpty(i.ClaimValue)
                            select new ClaimGroupItem
                            {
                                ClaimValue = i.ClaimValue,
                                MenuItemId = i.MenuItemId,
                            })
                .ToList();

        _repository.Update<ClaimGroup>(itemToSave);
        return RedirectToAction("Groups", new { updated = true });
    }
    return View(viewModel);
}

Entity Framework creating duplicate objects from list when object has composite Primary Key

2 Answers2