Why doesn't this SQL table/relationship work for [0..1] to [0..1] relationships?

Question

I'd like to do something similar to EF Code First 0..1 to 0..1 relationship but in EF Core rather than EF 5.

Essentially I want the following relationships, given these models, without using a junction table

class foo {
    public int FooId { get; set; }
    public string FooName { get; set; }
    public int Foo_BarId { get; set; }
}
class bar {
    public int BarId { get; set; }
    public string BarName { get; set; }
    public int Bar_FooId { get; set; }
}

Why is it not possible to do something like the following?

Note, FooId and BarId are Primary Keys [PK] and I'd like to set a FK relationship from Foo_BarId to BarId and similarly and FK relationship from Bar_FooId to FooId. Essentially, Foo_BarId and Foo_BarId can be null while FooId and BarId is required.

The resulting table for foo and bar would look like (i used a picture of html because its too hard to post html rendered stuff here)

Why is this not allowed? Or if it is, what are the magic commands for builder.HasOne.WithOne?

Answer 1

You specify a Bar_FooId value of 22 in two records in the Bar table. This is 1-to-many, not 1-to-1, from Foo to Bar. The 1 to [0..1] constraints will be violated.

A 1 to [0..1] relationship can use an unique index filtered for NOT NULL on the right. The filter allows more than one record with NULL for the "0" part of the relationship. Except for these NULLs, the values on the right are unique.

The index on the right side of a 1-to-many relationship can't be unique.

The other relationship, so far, is not 1-to-many. But it's too soon to tell.

EDIT:

Okay, I had it backwards. These relationships need to be in place and the filtered indexes are desired because you don't want the 1-to-many. Here is a total WAG as to what I'd do. I expect it to be wrong because I'm not a EF person.

class foo {
    public int FooId { get; set; }
    public string FooName { get; set; }
    [ForeignKey("BarId")] 
    public int Foo_BarId { get; set; }
}
class bar {
    public int BarId { get; set; }
    public string BarName { get; set; }
    [ForeignKey("FooId")] 
    public int Bar_FooId { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Bar>()
        .HasKey(z => z.BarID)
        .HasOne(a => a.Foo)
        .WithOne(b => b.Bar)
        .HasForeignKey<Bar>(b => b.Bar_FooId);

    modelBuilder.Entity<Foo>()
        .HasKey(z => z.FooID)
        .HasOne(a => a.Bar)
        .WithOne(b => b.Foo)
        .HasForeignKey<Foo>(b => b.Foo_BarId);
}

Check the database to insure you have the filtered unique indexes required. They are supported if adding manually according to this link. The filter on NULL is included automatically if .IsUnique() is specified. (You have to remove it if you don't want it filtered.)

https://learn.microsoft.com/en-us/ef/core/modeling/indexes

I'm old fashioned. This is easier to see in T-SQL for me. Feel free to mark me down for the blunder(s).