EF Core tag map UsingEntity, how to prevent it from re-inserting existing relations?

user_14992192 0
Viewed 2056

I'm trying to use a simple "tag map" system with EF Core 5. As far as I can tell from the docs I'm doing it correctly, but I keep running in to the issue that EF tries to re-insert already-existing items into the db and crashing:

SQLite Error 19: 'UNIQUE constraint failed: TagMap.FoosId, TagMap.TagsId'.

Here's a full example that generates the problem. The set-up is simple, there is a Foo class with a collection of Tags, and they are mapped with UsingEntity() to the "TagMap" table. It crashes on the second SaveChanges() when it tries to reinsert the first tag:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102] Failed executing DbCommand (65ms) [Parameters=[@p0='1' (DbType = String), @p1='1' (DbType = String)], CommandType='Text', CommandTimeout='30'] INSERT INTO "TagMap" ("FoosId", "TagsId") VALUES (@p0, @p1);

How can I get this to work?

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.IO;

namespace EfTagTest
{
    class Program
    {
        static void Main(string[] args)
        {
            SQLitePCL.Batteries.Init();

            var tags = new List<Tag>
            {
                new Tag() { Name = "one" },
                new Tag() { Name = "two" },
            };

            var foo = new Foo() { Name = "Foo1" };
            foo.Tags = new List<Tag>() { tags[0] };

            if (File.Exists("ett.db"))
            {
                File.Delete("ett.db");
            }

            using (var dbContext = new MyDbContext())
            {
                dbContext.Database.Migrate();
            }

            using (var dbContext = new MyDbContext())
            {
                dbContext.Foos.Add(foo);
                DisplayStates(dbContext.ChangeTracker.Entries());
                dbContext.SaveChanges();
            }

            foo.Tags.Add(tags[1]);

            using (var dbContext = new MyDbContext())
            {
                dbContext.Entry(foo).State = EntityState.Modified;
                DisplayStates(dbContext.ChangeTracker.Entries());
                dbContext.SaveChanges();
            }
        }

        private static void DisplayStates(IEnumerable<EntityEntry> entries)
        {
            foreach (var entry in entries)
            {
                Console.WriteLine($"Entity: {entry.Entity.GetType().Name}, State: { entry.State.ToString()}");
            }
        }
    }

    class MyDbContext : DbContext
    {
        public DbSet<Tag> Tags { get; set; }

        public DbSet<Foo> Foos { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Foo>()
                .HasMany(c => c.Tags)
                .WithMany(t => t.Foos)
                .UsingEntity(x => x.ToTable("TagMap"));
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Data Source=ett.db;");
            optionsBuilder.UseLoggerFactory(MyLoggerFactory);
            optionsBuilder.EnableSensitiveDataLogging();
            optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.TrackAll);
        }

        public static readonly ILoggerFactory MyLoggerFactory
            = LoggerFactory.Create(builder => { builder.AddConsole(); });
    }

    public class Tag
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public virtual ICollection<Foo> Foos { get; set; }
    }

    public class Foo
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public virtual ICollection<Tag> Tags { get; set; }
    }
}

The generated db looks exactly like you'd expect:

            migrationBuilder.CreateTable(
                name: "TagMap",
                columns: table => new
                {
                    FoosId = table.Column<int>(type: "INTEGER", nullable: false),
                    TagsId = table.Column<int>(type: "INTEGER", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_TagMap", x => new { x.FoosId, x.TagsId });
                    table.ForeignKey(
                        name: "FK_TagMap_Foos_FoosId",
                        column: x => x.FoosId,
                        principalTable: "Foos",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_TagMap_Tags_TagsId",
                        column: x => x.TagsId,
                        principalTable: "Tags",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateIndex(
                name: "IX_TagMap_TagsId",
                table: "TagMap",
                column: "TagsId");

And the project file:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="5.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="5.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="5.0.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="5.0.2">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.Extensions.Logging.Console" Version="5.0.0" />
  </ItemGroup>

</Project>
entity-framework-core entity-framework c#
1 Answers

What you've run into is utilizing the same object on two different dbContexts (you create it once for the first save, then you destroy it and create a second one...)

using (var dbContext = new MyDbContext()) {
    dbContext.Foos.Add(foo);
    DisplayStates(dbContext.ChangeTracker.Entries());
    dbContext.SaveChanges(); 
}

foo.Tags.Add(tags[1]);

using (var dbContext = new MyDbContext()) {
    dbContext.Entry(foo).State = EntityState.Modified;
    DisplayStates(dbContext.ChangeTracker.Entries());
    dbContext.SaveChanges(); 
}

If you only use one dbContext, you won't have to re-attach the "foo" object because changes to it will be tracked after you first attach it with the Add method dbContext.Foos.Add(foo). Subsequent changes will then be tracked correctly (even after the first SaveChanges()) allowing you to make MORE changes and SaveChanges() again - as long as you don't dispose of the dbContext that is doing the tracking.

So, to correct the quoted code above, use this instead:

using (var dbContext = new MyDbContext()) {
    dbContext.Foos.Add(foo); // <-- dbContext starts tracking changes to foo here.
    DisplayStates(dbContext.ChangeTracker.Entries());
    dbContext.SaveChanges();  // <-- Pending changes saved to datastore.

    foo.Tags.Add(tags[1]); // <-- Oh look, something is changing on a tracked entry!
    
    DisplayStates(dbContext.ChangeTracker.Entries());
    dbContext.SaveChanges(); // <-- Pending changes saved to datastore.
}

To go above and beyond the question, if you want to manipulate the tags on an existing foo object, you'll need to load it along with it's existing tags and manipulate those:

using (var dbContext = new MyDbContext()) {
    var myExistingFoo = dbContext.Foos
        .Include(x => x.Tags) // <-- dbContext will track the tags only if you include them.
        .Where(x => x.Id == 12) // <-- Just loading the one with Id = 12
        .First(); 
    DisplayStates(dbContext.ChangeTracker.Entries()); // <-- No changes yet.

    foo.Tags.Add(tags[2]); // <-- A new tag!
    // foo.Tags.AddRange(newTags); // <-- A whole bunch of new tags!
    // foo.Tags.RemoveRange(foo.Tags.Where(x => x.Name == "Bad Tag")); // <-- Remove one or more tags
    // foo.Tags.Clear(); // Delete all tags
    
    DisplayStates(dbContext.ChangeTracker.Entries()); // <-- Shows the new tag.
    dbContext.SaveChanges(); // <-- Only the new tag is added.
}
user_150581840
Related