c# - Entity Framework: Attaching an entity of type 'Country' failed because another entity of the same type already has the same primary key value

Question

I'm trying to simplify what is a much more complex data model, but for clarity let's assume I have two DB tables: tb_Item and tb_Country with an FK on the tb_Item table relating tb_Country.CountryId and tb_Item.MarketId.

In code, I have Item and Country classes, where Country is a member of Item, because I need to access some Country properties in the business logic (should not be relevant for this question though).

public class Item 
{
    public long ItemId { get; set; }
    public int? MarketId { get; set; }
    public string Name { get; set; }
    public virtual Country Country { get; set; }
}

public class Country
{
    public int CountryId { get; set; }
    public string Name { get; set; }
}

When I retrieve Item records from the DB to display in the UI, I do something like this (again, simplified):

using (var ctx = GetContext())
{
    var items = ctx.Items.Include(x => x.Country).AsQueryable();
}

var matching = Items.Where(d => d.AsOfDate == items.Where(d2 => 
d2.Country.IsoCountryCode == d.Country.IsoCountryCode && 
d2.AsOfDate <= asOfDate).Max(to2 => to2.AsOfDate));

Then, when I make any changes via the UI and save, this Save method is called:

public void SaveItems(IList<Item> items)
{
    _repo.ExecuteInTransaction(ctx =>
    {
        foreach (var item in items)
        {
            var existingItems = _repo.FindItems(
                item.AsOfDate.Date,
                item.MarketId)
            var existing = existingItems.FirstOrDefault(a => a.AsOfDate.Date == item.AsOfDate.Date);
            var createNewForAmend = existing == null && item.ItemId > 0;
            if (createNewForAmend)
            {
                existing = _repo.GetItemById(item.ItemId);
                existing.ItemId = 0;
                existing.AutomaticTouchDisabled = true;
            }
            _repo.SaveItem(ctx, item);
            if (!createNewForAmend) continue;
            _repo.SaveItem(ctx, existing);
        }
    });
}

Where FindItems is:

public IList<Item> FindItems(DateTime asOfDate, int? countryId)
{
    using (var ctx = GetContext())
    {
        var rawItems = ctx.Items.Include(x => x.Country).AsQueryable();

        var items = rawItems.Where(d => d.AsOfDate == rawItems.Where(d2 =>
                                                                d2.Country.IsoCountryCode == d.Country.IsoCountryCode && 
                                                                d2.AsOfDate <= asOfDate).Max(to2 => to2.AsOfDate));

        if (countryId.HasValue)
            items = items.Where(d => d.Country.CountryId == countryId.Value);

        return items.ToList();
    }
}

...GetItemById is:

public Item GetItemById(long id)
{
    using (var ctx = GetContext())
    {
        return ctx.Items.SingleOrDefault(c => c.ItemId == id);
    }
}

...and SaveItem is:

public void SaveItem(Context ctx, Item item)
{
    ctx.Items.Attach(item);
    ctx.Entry(item).State = EntityState.Modified;
    ctx.Items.AddOrUpdate(item);
    ctx.SaveChanges();
}

At the point where ctx.SaveChanges() is executed and I'm saving an Item record with a CountryId that already exists in the Country table, I get the dreaded EF error:

System.ServiceModel.FaultException: 'Attaching an entity of type 'MyService.Data.Model.Country' failed because another entity of the same type already has the same primary key value. This can happen when using the 'Attach' method or setting the state of an entity to 'Unchanged' or 'Modified' if any entities in the graph have conflicting key values. This may be because some entities are new and have not yet received database-generated key values. In this case use the 'Add' method or the 'Added' entity state to track the graph and then set the state of non-new entities to 'Unchanged' or 'Modified' as appropriate.'

My question is, I don't expect to have uniqueness constraint issues on the tb_Item table for duplicate countries (I expect a many:one relationship for items to country). It's almost as if the code is trying to save a duplicate country record to the tb_Country table when saving a record to the tb_Item table.

I'm sure I'm doing something stupid, I just can't figure out what it is...have tried the suggestions in other questions related to this: trying to detach the Country model, using AsNoTracking() when retrieving records, not doing the .Include(x => x.Country) when retrieving Item records, etc., but nothing quite works.

标签： c#sql.netsql-serverentity-framework