Why am I getting a UNIQUE constraint failed after adding an implicit many-to-many relationship?

[asfarley]

I have an application with some nested classes being stored into a database. The classes Controller which contains many Modules, which contains many TagGroups, which is many-to-many with Tags (that is: each TagGroup contains an ObservableCollection<Tag> and each Tag contains an ObservableCollection<TagGroup>.

This is a desktop WPF application where objects are loaded from the database context which is then disconnected, and re-added to a new database context when the user saves. My intention is for EF Core to automatically detect which objects are new vs. pre-existing based on whether the fk is populated, and have EF Core automatically decide whether to perform INSERT or UPDATE.

Previously, TagGroup was one-to-many with Tag (TagGroup contains an ObservableCollection<Tag> but Tag only contained ParentTagGroup and ParentTagGroupId). In this case, everything was working as expected. I was using Update() and UpdateRange() on parent Controller objects, expecting this function to traverse relationships and add or update as needed.

Now, after only making the change to modify the relationship between Tag and TagGroup to make it many-to-many, I'm getting a UNIQUE constraint violated from SQLite on save. This doesn't make sense to me, because shouldn't EF Core perform an UPDATE instead of an INSERT if the key already exists?

The name of the implicit class is TagTagGroup, which records the many-to-many relationship between Tag and TagGroup. I can see in the SQLite database that one TagTagGroup exists after creating a single Controller containing a single Module, containing a single TagGroup, with one TagGroup its list. The issue seems to be that if I open the application and load my nested classes, the TagTagGroup which was loaded out of the database gets re-inserted as another instance, presumably with the same fk value it got from the database.

Is this behavior expected? Is it obvious what I'm doing wrong?

Relevant sections of the TagGroup class:

    public class TagGroup: INPC
    {
        public int Id { get; set; }
        public ObservableCollection<Tag>? Tags { get; set; } = new ObservableCollection<Tag>();

Relevant sections of the Tag class:
public ObservableCollection<TagGroup> TagGroups { get; set; } = new ObservableCollection<TagGroup>();

Here's how the top-level Controller objects are loaded from the dbcontext before it's disconnected:
var virtualControllers = db.VirtualControllers.Include(c => c.Modules).ThenInclude(m => m.TagGroups).ThenInclude(tg => tg.Tags).Include(c => c.Tags).Include(c => c.Timers).Include(c => c.Equations).ThenInclude(eq => eq.Elements).ToList();

And here is how the objects are Updated (re-added to the dbcontext) which somehow results in TagTagGroups being duplicated or something:

manager.VirtualControllers.UpdateRange(virtualControllers);
manager.SaveChanges();

Here's the exception I'm getting:
SqliteException: SQLite Error 19: 'UNIQUE constraint failed: TagTagGroup.TagGroupsId, TagTagGroup.TagsId'.

One point of confusion for me is that I don't know how I can inspect the values in the TagTagGroup fields since this is an implicit table.

Include provider and version information

EF Core version: 9.0.0
Database provider: SQLite 3.13.0
Target framework: e.g. .NET 9.0
Operating system: Windows 11
IDE: e.g. Visual Studio 2022 17.12.3

[asfarley]

It's possible I'm running into this:
#28005

I'm relatively new to EF Core so I'm going to keep reading before confirming/closing as a duplicate.

[cincuranet]

Either way, this issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[asfarley]

I've created a small project to demo the issue:

https://github.com/asfarley/EfCoreM2MDemo

This project will create a database, add a couple of objects associated with a many-to-many relationship, save, load them again, then try to Update. This reproduces the issue as I'm seeing it.

I think the issue is probably visible from just the following code but I'm not certain:

var path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)  + "/Demo.sqlite";

var a1 = new AClass();
a1.AName = "First";

var b1 = new BClass();
b1.BName = "Second";

a1.BClasses.Add(b1);
b1.AClasses.Add(a1);

//First time: save to database
using (var db = new DManager(path))
{
   db.Database.Migrate();
   db.Update(a1);
   //db.Update(b1); //Why does this cause an error? Is Update() not idempotent?
   db.SaveChanges();
}

List<AClass>? readoutAClasses = null;
using (var db = new DManager(path))
{
   readoutAClasses = db.AClasses.Include(a => a.BClasses).ToList();
}

using (var db = new DManager(path))
{
   db.UpdateRange(readoutAClasses);
   db.SaveChanges();
}

[cincuranet]

The problem is you're calling UpdateRange here. That means your AClass and BClass will end up in Modified state and AClassBClass will be Added. The documentation describes the behavior well.

You can't just use FKs to detect new/existing, that's not going to work in general. Look at methods like AttachRange and TrackGraph, with the help of these, and bit of code, you should be able to do what you're trying to do.