How to define Self referencing one to one relationship

[niltor]

How to define Self referencing one to one relationship?

I have a model:

public class User
{
    public string Name{get;set;}
    // your wife or husband
    public User Spouse{get;set;}
}

Of course ,you just have one wife or husband !
I wan't to select personA （include person A' couple)!

when I use code first ,and use dotnet ef database update ,
The error here:

There are no primary or candidate keys in the referenced table!

Further technical details

EF Core version: (2.1.1)
Database Provider: ( Microsoft.EntityFrameworkCore.SqlServer)
Operating system: Win10 1803
IDE: (Visual Studio 2017 15.7.5)

[ajcvickers]

@niltor This is actually quite tricky if you want to have a single navigation property Spouse, so let me first show how to do it without that:

public class User
{
    public string Name { get; set; }
    public User Husband { get; set; }
    public User Wife { get; set; }
}

public class BloggingContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<User>(b =>
        {
            b.HasKey(e => e.Name);
            b.HasOne(e => e.Husband).WithOne(e => e.Wife);
        });
    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Add(new User {Name = "Arthur", Wife = new User{ Name = "Wendy" } });
            context.SaveChanges();
        }

        using (var context = new BloggingContext())
        {
            foreach (var user in context.Set<User>().Include(e => e.Husband).Include(e => e.Wife).ToList())
            {
                Console.WriteLine($"{user.Name} is married to {(user.Wife ?? user.Husband).Name}");
            }
        }
    }
}

In this case there is a single relationship with a navigation property pointing to Wife and an inverse navigation property pointing back to the husband. This acts as a normal 1:1 relationship. A person can be set to have a Wife, or a person can be set to have a Husband.

Now the problem with having a single navigation property Spouse is that it can't be used for both directions of the relationship--that is, it can't be used to point both from a Husband to a Wife and a Wife to a Husband. To work around this, we can define the relationship as not having an inverse navigation property: b.HasOne(e => e.Spouse).WithOne(). We can then use this relationship for both Husband and Wife, but for each couple the relationship needs to be set for both directions--that is the Wife needs to be set on the Husband, and the Husband needs to be set on the Wife. (Since this can create cycles you may run into #1699.) Here's the full example:

public class User
{
    public string Name { get; set; }
    public User Spouse { get; set; }
}

public class BloggingContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<User>(b =>
        {
            b.HasKey(e => e.Name);
            b.HasOne(e => e.Spouse).WithOne();
        });
    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var arthur = new User {Name = "Arthur"};
            var wendy = new User {Name = "Wendy"};

            context.AddRange(arthur, wendy);
            context.SaveChanges();

            arthur.Spouse = wendy;
            wendy.Spouse = arthur;

            context.SaveChanges();
        }

        using (var context = new BloggingContext())
        {
            foreach (var user in context.Set<User>().Include(e => e.Spouse).ToList())
            {
                Console.WriteLine($"{user.Name} is married to {user.Spouse.Name}");
            }
        }
    }
}

[niltor]

@ajcvickers THX!