"Attaching an entity of type T failed because another entity of the same type already has the same primary key value"

Question

I have a Language model defined as such:

public class Language
{
    [JsonProperty("iso_639_1")]
    public string Iso { get; set; }

    [JsonProperty("name")]
    public string Name { get; set; }

    public override bool Equals(object obj)
    {
        if (!(obj is Language))
        {
            return false;
        }

        return ((Language)obj).Iso == Iso;
    }

    public override int GetHashCode()
    {
        return Iso.GetHashCode();
    }
}

This is used in a model Movie as ICollection<Language> SpokenLanguages. I am seeding my database using the information I gather. When multiple movies use the same language, I obviously want to re-use the existing entry in the Languages table.

The following achieves that by re-using the existing types and adding new ones:

var localLanguages = context.Languages.ToList();
var existingLanguages = localLanguages.Union(movie.SpokenLanguages);
var newLanguages = localLanguages.Except(existingLanguages).ToList();
newLanguages.AddRange(existingLanguages);
movie.SpokenLanguages = newLanguages;

This works but obviously this is rather ugly and not EF-friendly. I'm looking into attaching the existing models to EF and have it automatically re-use it but I can't seem to get it to work -- I end up with this error message:

Attaching an entity of type 'Models.Movies.Language' 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.

The code in question is this:

var localLanguages = context.Languages.ToList();
foreach (var language in movie.SpokenLanguages)
{
    if (localLanguages.Contains(language))
    {
        context.Languages.Attach(language);
        // no difference between both approaches
        context.Entry(language).State = EntityState.Unchanged;
    }
}

Setting the state as Unchanged or Modified does not make a difference. The JSON response I receive is

{
    "iso_639_1": "en",
    "name": "English"
}

These values are exactly the same as those existing in the database, both fields.

Each insertion in the database creates a new context and disposes of it.

How can I get EF to re-use the existing language entries instead of having to sift through them myself?

Answer 1

I have edited the model so it now includes a field Id and use that as primary key. Everything else, including equality comparisons, have remained the same. I now receive a different error message which might shed some more light on the issue:

{"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.MovieLanguages_dbo.Languages_LanguageId". The conflict occurred in database "MoviePicker", table "dbo.Languages", column 'Id'. The statement has been terminated."}

Additional information: An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.

I logged the SQL statements in the datacontext and this was the last executed statement:

INSERT [dbo].[MovieLanguages]([MovieId], [LanguageId])
VALUES (@0, @1)

-- @0: '2' (Type = Int32)  
-- @1: '0' (Type = Int32)

This shows that the LanguageId (field Id in table Language) is not filled in. This makes sense because it is 0 by default and all I do with it is attaching it to the EF configuration. This doesn't make it assume the value of the already existing object, causing a FK constraint error because it's trying to create a reference to an entry with ID 0 which does not exist.

Knowing this, I went for a combination of what I had and what I was aiming to do. First I look whether the language is already inside the database. If it isn't, everything stays normal and I simply insert it. If it is already in there, I assign its ID to the new Language object, detach the existing object and attach the new one.

Essentially I swapped the object that EF keeps track off. It would've been very helpful if it would do this by itself when it notices object equality but until it does that, this is the best I came up with.

var localLanguages = _context.Languages.ToList();
foreach (var language in movie.SpokenLanguages)
{
    var localLanguage = localLanguages.Find(x => x.Iso == language.Iso);
    
    if (localLanguage != null)
    {
        language.Id = localLanguage.Id;
        _context.Entry(localLanguage).State = EntityState.Detached;
        _context.Languages.Attach(language);
    }
}