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.

CodePudding user response：

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