CODEWITHSUNDEEP
.netasp.net-coreentity-framework-coreasp.net-core-2.1entity-framework-core-2.1
john
john

Reputation: 13

Many to many self reference table - guid Id changes itself

I have a many to many table that references. Itself it sounds crazy so here's an image for a better view of the problem https://i.sstatic.net/lj2zZ.png

I use guids as primary and foreign keys. When I try to add a new instance of Foo to the database which has a relationship to some foo from the database, and on the exact moment when the line myDbContext.Set<Foo>().Add(foo); is passed, the Guid https://i.sstatic.net/WlFW9.png gets changed to https://i.sstatic.net/989nb.png

Code to create database: 

 internal class Foo
    {
        public Guid Id { get; set; }
        public string Title { get; set; }

        public virtual List<DependencyFoo> Dependents { get; set; }
        public virtual List<DependencyFoo> DependentsOf { get; set; }
    }

   internal class DependencyFoo
    {
        public virtual Foo Dependent { get; set; }
        public Guid DependentId { get; set; }

        public virtual Foo DependentOf { get; set; }
        public Guid DependentOfId { get; set; }
    }

internal class MyDbContext : DbContext
    {
        public DbSet<Foo> Foos { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Data Source=.\\SQLEXPRESS;Initial Catalog=fooDb;Trusted_Connection=True;Integrated Security=True");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<DependencyFoo>()
              .HasKey(x => new { x.DependentOfId, x.DependentId });

            modelBuilder.Entity<DependencyFoo>()
                .HasOne(x => x.DependentOf)
                .WithMany(x => x.DependentsOf)
                .HasForeignKey(x => x.DependentOfId)
                .OnDelete(DeleteBehavior.Restrict);

            modelBuilder.Entity<DependencyFoo>()
                .HasOne(x => x.Dependent)
                .WithMany(x => x.Dependents)
                .HasForeignKey(x => x.DependentId);
        }
    }

Actual testing:

internal class Program
    {
        private static void Main(string[] args)
        {
            MyDbContext myDbContext = new MyDbContext();
            myDbContext.Set<Foo>().Add(new Foo { Title = "Some cool title" });
            myDbContext.SaveChanges();

            Foo fooDb = myDbContext.Set<Foo>().FirstOrDefault(x => x.Title == "Some cool title");
            DependencyFoo dependencyFoo = new DependencyFoo
            {
                DependentOfId = fooDb.Id
            };

            Foo foo = new Foo
            {
                DependentsOf = new List<DependencyFoo>()
            };
            foo.DependentsOf.Add(dependencyFoo);

            myDbContext.Set<Foo>().Add(foo);
        }
    }

Any idea why this is happening? Does it have to do something with my

relationship or ? PS (the example is simplified with foos :))

Upvotes: 1

Views: 542

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205589

I believe the problem is with mappings Foo.Dependents <-> DependencyFoo.Dependent and Foo.DependentsOf <-> DependencyFoo.DependentOf.

The actual dependents/dependents of foo should really be something like (pseudocode):

IEnumerable<Foo> fooDependents = db.Set<DependencyFoo>()
    .Where(d => d.DependentOf.Id == foo.Id)
    .Select(d => d.Dependent);

IEnumerable<Foo> fooDependentsOf = db.Set<DependencyFoo>()
    .Where(d => d.Dependent.Id == foo.Id)
    .Select(d => d.DependentOf);

Note how the roles of collections in the main entity and reference navigation properties in the link entity are exchanged. To get the dependents, you have to select linked dependent filtered by dependent of. And vice versa.

Currently you set dependencyFoo.DependentOfId = fooDb.Id and also foo.DependentsOf.Add(dependencyFoo);. Both they represents one and the same, so when you call add, the EF Core fixup generates new Guid for for.Id and assigns it to dependencyFoo.DependentOfId. At the same time, dependencyFoo.Dependent remains null and since dependencyFoo.DependentId value is Guid.Empty, EF Core creates and assigns a new Guid to it.

To fix the issue, simply exchange the collections in the fluent mapping:

modelBuilder.Entity<DependencyFoo>()
    .HasOne(x => x.DependentOf)
    .WithMany(x => x.Dependents)
    .HasForeignKey(x => x.DependentOfId)
    .OnDelete(DeleteBehavior.Restrict);

modelBuilder.Entity<DependencyFoo>()
    .HasOne(x => x.Dependent)
    .WithMany(x => x.DependentsOf)
    .HasForeignKey(x => x.DependentId);

or alternatively (the effect is the same, I just find it easier to read and follow):

modelBuilder.Entity<Foo>()
    .HasMany(x => x.Dependents)
    .WithOne(x => x.DependentOf)
    .HasForeignKey(x => x.DependentOfId)
    .OnDelete(DeleteBehavior.Restrict);

modelBuilder.Entity<Foo>()
    .HasMany(x => x.DependentsOf)
    .WithOne(x => x.Dependent)
    .HasForeignKey(x => x.DependentId);

Upvotes: 1

Related Questions