Entity Framework Core One-One Self Referencing Relationship fails

Question

When building the migration I get the following error:

Unable to determine the relationship represented by navigation property 'Location.NorthLocation' of type 'Location'. Either manually configure the relationship, or ignore this property from the model.

The Location entity:

public class Location
{
    public Guid Id { get; set; }

    public DateTime CreatedWhen { get; set; }
    public string CreatedBy { get; set; }
    public DateTime ModifiedWhen { get; set; }
    public string ModifiedBy { get; set; }

    public Guid? NorthLocationId { get; set; }
    public virtual Location NorthLocation { get; set; }

    public Guid? SouthLocationId { get; set; }
    public virtual Location SouthLocation { get; set; }

    public Guid? EastLocationId { get; set; }
    public virtual Location EastLocation { get; set; }

    public Guid? WestLocationId { get; set; }
    public virtual Location WestLocation { get; set; }

}

The type configuration:

public class MyContext : DbContext
{
    public MyContext(DbContextOptions<MyContext> options) : base(options)
    {
    }

    public DbSet<Location> Locations { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.Entity<T>().HasKey("Id");
        builder.Entity<T>().Property("Id").ValueGeneratedOnAdd();
        builder.Entity<T>().Property("CreatedWhen").HasDefaultValueSql("GETDATE()").ValueGeneratedOnAdd();
        builder.Entity<T>().Property("ModifiedWhen").IsRequired();
        builder.Entity<T>().Property("CreatedBy").HasMaxLength(50).IsRequired();
        builder.Entity<T>().Property("ModifiedBy").HasMaxLength(50).IsRequired();

        // Locations
        builder.Entity<Location>().HasOne(x => x.NorthLocation).WithOne(x => x.SouthLocation).HasForeignKey(typeof(Location), "NorthLocationId").OnDelete(DeleteBehavior.SetNull);
        builder.Entity<Location>().HasOne(x => x.SouthLocation).WithOne(x => x.NorthLocation).HasForeignKey(typeof(Location), "SouthLocationId").OnDelete(DeleteBehavior.SetNull);
        builder.Entity<Location>().HasOne(x => x.EastLocation).WithOne(x => x.WestLocation).HasForeignKey(typeof(Location), "EastLocationId").OnDelete(DeleteBehavior.SetNull);
        builder.Entity<Location>().HasOne(x => x.WestLocation).WithOne(x => x.EastLocation).HasForeignKey(typeof(Location), "WestLocationId").OnDelete(DeleteBehavior.SetNull);
    }

}

My goal is to have a Location entity that self-references it's own neighbours to the north/south/east/west.

Can anyone suggest why I might be getting this error?

score 5 · Accepted Answer · edited Jan 06 '20 at 15:50

Your model configuration is incorrect because it maps each of the navigation properties twice. E.g. SouthLocation is mapped both as the reverse navigation for the NorthLocationId foreign key and as the direct navigation for SouthLocationId.

Each navigation property (i.e. NorthLocation, SouthLocation, EastLocation, WestLocation) can only be mapped to one relationship (i.e. to one foreign key).

If I delete the 2nd and 4th lines of the relationship configuration part, the model seems to be functioning properly.

In general in EF Core we try to deal with conflicting configuration by letting the last configuration to execute win, but this has some limitations and it is hard to anticipate what can happen when you execute this code. Certainly, using EF Core 2.0 preview1 against SQL Server, I got a different exception (an error form SQL Server, complaining about cyclic dependencies in cascade delte), so it is possible that we have improved how we handle this scenario.

Here is the code:


    using System;
    using Microsoft.EntityFrameworkCore;
    using Microsoft.EntityFrameworkCore.Metadata;

    namespace ConsoleApp4
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var db = new MyContext())
                {
                    db.Database.EnsureDeleted();
                    db.Database.EnsureCreated();
                }
            }
        }

        public class MyContext : DbContext
        {
            public DbSet<Location> Locations { get; set; }
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseSqlServer(@"server=(localdb)\mssqllocaldb;database=hey;ConnectRetryCount=0");
            }

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Location>().HasKey("Id");
                modelBuilder.Entity<Location>().Property("Id")
                   .ValueGeneratedOnAdd();
                modelBuilder.Entity<Location>().Property("CreatedWhen")
                   .HasDefaultValueSql("GETDATE()")
                   .ValueGeneratedOnAdd();
                modelBuilder.Entity<Location>().Property("ModifiedWhen")
                   .IsRequired();
                modelBuilder.Entity<Location>().Property("CreatedBy")
                   .HasMaxLength(50)
                   .IsRequired();
                modelBuilder.Entity<Location>().Property("ModifiedBy")
                   .HasMaxLength(50)
                   .IsRequired();
                modelBuilder.Entity<Location>()
                   .HasOne(x => x.NorthLocation)
                   .WithOne(x => x.SouthLocation)
                   .HasForeignKey(typeof(Location), "NorthLocationId")
                   .OnDelete(DeleteBehavior.Restrict);
                modelBuilder.Entity<Location>()
                   .HasOne(x => x.EastLocation)
                   .WithOne(x => x.WestLocation)
                   .HasForeignKey(typeof(Location), "EastLocationId")
                   .OnDelete(DeleteBehavior.Restrict);
            }
        }

        public class Location
        {
            public Guid Id { get; set; }
            public DateTime CreatedWhen { get; set; }
            public string CreatedBy { get; set; }
            public DateTime ModifiedWhen { get; set; }
            public string ModifiedBy { get; set; }
            public Guid? NorthLocationId { get; set; }
            public virtual Location NorthLocation { get; set; }
            public Guid? SouthLocationId { get; set; }
            public virtual Location SouthLocation { get; set; }
            public Guid? EastLocationId { get; set; }
            public virtual Location EastLocation { get; set; }
            public Guid? WestLocationId { get; set; }
            public virtual Location WestLocation { get; set; }
        }
    }

If those two lines are excluded how does that work with the corresponding foreign key I'd properties? Is ef clever enough to figure it out and populate them still? — Hades, May 29 '17 at 07:53
You would only really have one FK in the database for each south-north and for each east-west relationship. The choice of where to locate the FK (e.g. either the north or the south row) is completely arbitrary. This is in turn more of a relational database design question than EF Core question. BTW, I haven't so far mentioned it, but I am not sure that keeping track of north-south and east-west relationships is a good way to model actual geographically distributed locations. — divega, May 29 '17 at 12:52
I tried removing the two lines and it still throws the same error for me. It's for a space map for a strategy game. Essentially I want to have the ability to "travel" in any direction. — Hades, May 29 '17 at 18:54
With 1.1.2 I after removing the two reverse relationship configurations I get the following error: System.Data.SqlClient.SqlException occurred HResult=0x80131904 Message=Introducing FOREIGN KEY constraint 'FK_Locations_Locations_EastLocationId' on table 'Locations' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors. This is due to a limitation imposed by SQL Server. — divega, May 30 '17 at 04:08
After changing the OnDelete() calls to use DeleteBehavior.Restrict, I am using the Location class without changes, but had to fix a couple of things that didn't compile in the rest of the code. I will update my answer to contain the full listing. Feel free to respond in a comment if there is anything missing. — divega, May 30 '17 at 04:14

score 1 · Answer 2 · answered Mar 26 '21 at 11:22

1

I was able to fix the same issue by just adding .HasOne() to the main model

    // Locations
    builder.Entity<Location>().HasOne(x => x.NorthLocation);
    builder.Entity<Location>().HasOne(x => x.SouthLocation);
    builder.Entity<Location>().HasOne(x => x.EastLocation);
    builder.Entity<Location>().HasOne(x => x.WestLocation);

answered Mar 26 '21 at 11:22

Boncho Valkov

1,319
10
10

Don't you also need `HasForeignKey()` call here? – James Poulose Jan 16 '22 at 08:21

Entity Framework Core One-One Self Referencing Relationship fails

2 Answers2

Linked