Error "Database operation expected to affect 1 row(s) but actually affected 0 row(s)" when adding a related entity

Question

I've been searching for a solution to write a "generic" update method in EF Core which updates all changed properties of an entity including a related collection. The reason for this is that I store translations for the name of an entity in a different table. I found this solution which seemed to work just fine at the beginning but then I noticed that I get an error "Database operation expected to affect 1 row(s) but actually affected 0 row(s)" when the only thing I changed in the entity was adding a new name translation in the related table TblProjectTranslations. Here is my code:

public async Task UpdateProjectAsync(TblProject updatedEntity)
{
    TblProject dbEntity = Context.TblProjects
        .Include(dbEntity => dbEntity.TblProjectTranslations)
        .SingleOrDefault(dbEntity => dbEntity.ProjectId == updatedEntity.ProjectId);

    if (dbEntity != null)
    {
        Context.Entry(dbEntity).CurrentValues.SetValues(updatedEntity);

        foreach (TblProjectTranslation dbTranslation in dbEntity.TblProjectTranslations.ToList())
        {
            if (!updatedEntity.TblProjectTranslations
                .Any(translation => translation.ProjectId == dbTranslation.ProjectId && translation.Language == dbTranslation.Language))
            {
                Context.TblProjectTranslations.Remove(dbTranslation);
            }
        }                  

        foreach (TblProjectTranslation newTranslation in updatedEntity.TblProjectTranslations)
        {
            TblProjectTranslation dbTranslation = dbEntity.TblProjectTranslations
                .SingleOrDefault(dbTranslation => dbTranslation.ProjectId == newTranslation.ProjectId && dbTranslation.Language == newTranslation.Language);

            if (dbTranslation != null)
            {
                Context.Entry(dbTranslation).CurrentValues.SetValues(newTranslation);
            }
            else
            {
                dbEntity.TblProjectTranslations.Add(newTranslation);
            }
        }

        await Context.SaveChangesAsync();
    }
}

Here are the reverse engineered EF Core classes:

public partial class TblProject
{
    public TblProject()
    {
        TblProjectTranslations = new HashSet<TblProjectTranslation>();
    }

    public int ProjectId { get; set; }

    public virtual ICollection<TblProjectTranslation> TblProjectTranslations { get; set; }
}

public partial class TblProjectTranslation
{
    public int ProjectId { get; set; }
    public string Language { get; set; }
    public string ProjectName { get; set; }

    public virtual TblProject Project { get; set; }
}

Here is how they are defined in OnModelCreating:

modelBuilder.Entity<TblProject>(entity =>
{
    entity.HasKey(e => e.ProjectId);

    entity.ToTable("TBL_project");

    entity.Property(e => e.ProjectId).HasColumnName("project_ID");
});

modelBuilder.Entity<TblProjectTranslation>(entity =>
{
    entity.HasKey(e => new { e.ProjectId, e.Language });

    entity.ToTable("TBL_project_translation");

    entity.HasIndex(e => e.ProjectId, "IX_TBL_project_translation_project_ID");

    entity.Property(e => e.ProjectId).HasColumnName("project_ID");

    entity.Property(e => e.Language)
        .HasMaxLength(5)
        .HasColumnName("language")
        .HasDefaultValueSql("('-')");

    entity.Property(e => e.ProjectName)
        .IsRequired()
        .HasMaxLength(50)
        .HasColumnName("project_name")
        .HasDefaultValueSql("('-')");

    entity.HasOne(d => d.Project)
        .WithMany(p => p.TblProjectTranslations)
        .HasForeignKey(d => d.ProjectId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("TBL_project_translation_TBL_project");
});

What I do not understand is that dbEntity.TblProjectTranslations.Add(newTranslation) seems to be the problem. When I replace this line with await Context.TblProjectTranslations.AddAsync(newTranslation), the error "magically" disappears, but aren't both ways supposed to do basically the same thing? Here is an example for an updatedEntity and a dbEntity which I captured while debugging the function right before the problem occurred:

updatedEntity:
ProjectId: 41
TblProjectTranslations: 1 Entry with:
    Language: "en"
    Project: null
    ProjectId: 41
    ProjectName: "TestNameEN"

dbEntity:
ProjectId: 41
TblProjectTranslations: No entries

What is going on here? I do not even have any triggers in the database in both of those tables which seemed to be the cause of this error for some other people.

Answer 1

After playing a bit with the sample model, code and explanations, I finally was able to reproduce it. The culprit seems to be the composite key and the default value for the string part of it:

entity.HasKey(e => new { e.ProjectId, e.Language }); // (1)

entity.Property(e => e.Language)
    .HasMaxLength(5)
    .HasColumnName("language")
    .HasDefaultValueSql("('-')"); // (2)

Same happens if you use

.HasDefaultValue("-")

This combination somehow is causing the EF to consider the item added to the parent collection navigation property

dbEntity.TblProjectTranslations.Add(newTranslation);

as Modified instead of Added, which later leads to the error in questtion.

Since I can't find such behavior explanation in the EF Core documentation, and in general it looks wrong, I would suggest to go and report it to the EF Core GitHub issue tracker.

Meanwhile, the possible workarounds I see are

• Remove .HasDefaultValueSql / .HasDefaultValue for the key column

• Instead of adding the child to the parent collection, add it directly to the context or the corresponding DbSet (optionally set the reference navigation property in advance "just in case"):

newTranslation.Project = dbEntity;
Context.Add(newTranslation);