Howto update auto-implemented (by convention) many to many in Entity Framework Core 6 (.Net 6)

Question

I use EF Core 6 (not "classic" EF6) in ASP .NET Core 6 Web App (Razor pages) and I have the following entities and context (simplified):

public class Task
{
   public Task()
   {
       PlaneTypes = new HashSet<PlaneType>();
   }

   public Int64 Id { get; set; }
   public String Name { get; set; }
   public virtual ICollection<PlaneType> PlaneTypes { get; set; }
}

public class PlaneType
{
   public PlaneType()
   {
       Tasks = new HashSet<Task>();
   }

   public Int64 Id { get; set; }
   public String Name { get; set; }
   public virtual ICollection<Task> Tasks { get; set; }
}

public partial class PlanDbContext : DbContext
{
    public PlanDbContext()   {   }

    public PlanDbContext(DbContextOptions<PlanDbContext> options)
        : base(options)   {   }

    public virtual DbSet<PlaneType> PlaneTypes { get; set; }        
    public virtual DbSet<Task> Tasks { get; set; }
    //some other DBSets

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlite("data source=DBPlanov.db;Pooling=False;");
            optionsBuilder.ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning));
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
         modelBuilder.Entity<Task>().HasIndex(t => new { t.Name }).IsUnique();
         modelBuilder.Entity<PlaneType>().HasIndex(pt => new { pt.Name }).IsUnique();
    }

As you can see I have no distinct entity for many-to-many table (it is auto-implemented by EF Core 6 and I don't need to configure it - it is working without explicitly mapping the join table):

CREATE TABLE "PlaneTypeTask" (
    "PlaneTypesId" INTEGER NOT NULL,
    "TasksId" INTEGER NOT NULL,
    CONSTRAINT "PK_PlaneTypeTask" PRIMARY KEY ("PlaneTypesId", "TasksId"),
    CONSTRAINT "FK_PlaneTypeTask_PlaneTypes_PlaneTypesId" FOREIGN KEY ("PlaneTypesId") REFERENCES "PlaneTypes" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_PlaneTypeTask_Tasks_TasksId" FOREIGN KEY ("TasksId") REFERENCES "Tasks" ("Id") ON DELETE CASCADE
);

CREATE INDEX "IX_PlaneTypeTask_TasksId" ON "PlaneTypeTask" ("TasksId");

I have an ASP page to edit a Task where name and a list of PlaneTypes can be changed. My page model has:

[BindProperty]
public TaskEditModel CurrentTaskEditModel { get; set; }

where TaskEditModel is:

public class TaskEditModel
{
   public Task Task { get; set; }

   //some other properties
}

In code-behind I get edited task data from database with the following code:

public PartialViewResult OnGetShowTaskEdit(Int64 taskId)
{
     CurrentTaskEditModel = new();
     CurrentTaskEditModel.Task = _context.Tasks.
     Include(t => t.PlaneTypes).AsSplitQuery().SingleOrDefault(t => t.Id == taskId);
     ...
     return Partial("_TaskEditPartial", CurrentTaskEditModel);
}

After task is edited I save it with the following code:

...
if (TryValidateModel(CurrentTaskEditModel.Task))
{
     using (var transaction = _context.Database.BeginTransaction())
     {
          try
          {
               //Option1: NOT working - PlaneTypeTask table is NOT filled
               _context.Attach(CurrentTaskEditModel.Task).State = EntityState.Modified;

               //Option2: NOT working also - SqliteException (SQLite Error 19: 'UNIQUE 
               //constraint failed: PlaneTypeTask.PlaneTypesId, PlaneTypeTask.TasksId')
               _context.Update(CurrentTaskEditModel.Task);

               await _context.SaveChangesAsync();
               transaction.Commit();              
           }
           catch
           {
               transaction.Rollback();
           }
}
...

So load a Task with one PlaneType in its list. And I try to save it with edited name and with another PlaneType added to a list:

1. If I use Option1 - PlaneTypeTask table is not filled with an added PlaneType data.
2. If I use Option2 - UNIQUE constraint failed exception is thrown.
3. If I comment both lines and use only _context.SaveChangesAsync() - no changes are saved to database at all.

So what is my mistake?

Update

I've created a ConsoleApp to test my DbContext:

//emulate fetching my object from database on page load - OnGetShowTaskEdit
var CurrentTaskEditModel = new TaskEditModel();
using (var context = new PlanDbContext())
{
    CurrentTaskEditModel.Task = context.Tasks.Include(p => p.PlaneTypes).AsSplitQuery().First();
}

//emulate user edited Name on page
CurrentTaskEditModel.Task.Name = "Edited name";

//emulate saving object to database on post
using (var context = new PlanDbContext())
{
    using (var transaction = context.Database.BeginTransaction())
    {
        try
        {
            //emulate user added a PlaneType on page
            var planeTypeToAdd = context.PlaneTypes.Include(t => t.Tasks).AsSplitQuery().First();
            CurrentTaskEditModel.Task.PlaneTypes.Add(planeTypeToAdd);

            //NOT working - PlaneTypeTask table is NOT filled
            context.Attach(CurrentTaskEditModel.Task).State = EntityState.Modified;
            context.SaveChanges();
            transaction.Commit();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
        }
    }
}

It has same problems like in my Web App - so looks like something is wrong with my approach.

I've expected auto-implemented many-to-many to just work without explicitly mapping the join table and directly working with it. What is my mistake?

Answer 1

Arthur Vickers (aka ajcvickers) provided a solution on github:

This needs improvement--see #28005. For now, the best thing to do is attach the disconnected graph first, then mark entities as Modified as appropriate, and finally make changes to the graph. For example:

context.Attach(CurrentTaskEditModel.Task);
context.Entry(CurrentTaskEditModel.Task).State = EntityState.Modified;
CurrentTaskEditModel.Task.PlaneTypes.Add(planeTypeToAdd);

This then generated the following SQL:

INSERT INTO [PlaneTypeTask] ([PlaneTypesId], [TasksId])
VALUES (@p0, @p1);
UPDATE [Task] SET [Name] = @p2
OUTPUT 1
WHERE [Id] = @p3;