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

Answer 2

Try implementing the IEquatable<T> interface on your Language entity (I assume Iso is the entity primary key):

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

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

    public override bool Equals(object obj)
    {
        return Equals(other as Language);
    }

    public bool Equals(Langauge other)
    {
        // instance is never equal to null
        if (other == null) return false;

        // when references are equal, they are the same object
        if (ReferenceEquals(this, other)) return true;

        // when either object is transient or the id's are not equal, return false
        if (IsTransient(this) || IsTransient(other) ||
            !Equals(Iso, other.Iso)) return false;

        // when the id's are equal and neither object is transient
        // return true when one can be cast to the other
        // because this entity could be generated by a proxy
        var otherType = other.GetUnproxiedType();
        var thisType = GetUnproxiedType();
        return thisType.IsAssignableFrom(otherType) ||
            otherType.IsAssignableFrom(thisType);
    }

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

    private static bool IsTransient(Language obj)
    {
        // an object is transient when its id is the default
        // (null for strings or 0 for numbers)
        return Equals(obj.Iso, default(string));
    }

    private Type GetUnproxiedType()
    {
        return GetType(); // return the unproxied type of the object
    }
}

Now with this, try again:

var localLanguages = context.Languages.ToList(); // dynamic proxies
foreach (var language in movie.SpokenLanguages) // non-proxied
{
    if (localLanguages.Any(x => x.Equals(language)))
    {
        context.Entry(language).State = EntityState.Modified;
    }
}

Since EF uses dynamic proxies for entity instances that are loaded from the context, I am wondering if the Contains was coming back as an unexpected false value. I believe that Contains will only do a reference comparison, not an Equals comparison. Since the entities retrieved from the context are dynamic proxy instances, and your movie.SpokenLanguages are not, Contains may not have been comparing as you expected.

Reference: https://msdn.microsoft.com/en-us/library/ms131187(v=vs.110).aspx

The IEquatable interface is used by generic collection objects such as Dictionary, List, and LinkedList when testing for equality in such methods as Contains, IndexOf, LastIndexOf, and Remove. It should be implemented for any object that might be stored in a generic collection.