One-to-One optional relationship with both ends optional and both FKs

Question

I'm following this question: EF Code First - 1-to-1 Optional Relationship

And I have my relationships set up as follows:

public class Review {
    [Key]
    public int ReviewId { get; set; }

    public virtual Payment Payment { get; set; }
}

public class Payment {
    [Key]
    public int PaymentId { get; set; }

    [ForeignKey("Review")]
    public int? ReviewId { get; set; }
    public virtual Review Review { get; set; }
}

    public class ReviewConfiguration : EntityTypeConfiguration<Review>
{
    public ReviewConfiguration()
    {
        // One-to-One Optional
        HasOptional<Payment>(s => s.Payment).WithOptionalDependent(s => s.Review).Map(s => s.MapKey("PaymentId"));
    }
}

And I get ONE of the keys valid, but the other is never mapped as an optional FK:

What am I doing wrong?

I've seen some weird hacky solutions involving creating empty Lists etc - not what I'm looking for. I'm looking for a proper approach here - it has to exist... right?

Update

I'm using the above now - when I have the Payment on hand and need to access or delete the Review, I have to do another lookup on the [pseudo-FK] ReviewId, which totally sucks.

Answer 1

In any one-to-one association, EF uses only one foreign key. When the association is required, the foreign key will also be the primary key of the dependent entity, as explained here.

When the association is optional, both entities should be able to exist independent of one another. So their primary keys can't be foreign keys, because PKs can't be optional. Here an additional nullable FK field is required to establish the optional association.

In your case, technically it doesn't really matter which entity has the FK field (logically, it may). I've used this model:

public class Review
{
    [Key]
    public int ReviewId { get; set; }
    public virtual Payment Payment { get; set; }
}

public class Payment
{
    [Key]
    public int PaymentId { get; set; }

    public Review Review { get; set; }
}

With this mapping:

public class ReviewConfiguration : EntityTypeConfiguration<Review>
{
    public ReviewConfiguration()
    {
        // One-to-One Optional
        HasOptional(s => s.Payment)
            .WithOptionalDependent(s => s.Review)
            .Map(s => s.MapKey("PaymentId"));
    }
}

(So, apart from Payment.ReviewId, this is identical to the model + mapping in your question).

Now I can do things like ...

db.Set<Review>().Add(new Review { Payment = new Payment() });
db.Set<Payment>().Add(new Payment { Review = new Review() });
db.SaveChanges();

... where db of course is a context. The content of both tables now is:

PaymentId
-----------
1
2

ReviewId    PaymentId
----------- -----------
1           1
2           2

And I can query the data bidirectionally like so:

var data = db.Set<Review>().Include(r => r.Payment).ToList();

or

var data = db.Set<Payment>().Include(r => r.Review).ToList();

But instead of ReviewConfiguration, I can also use...

public class PaymentConfiguration : EntityTypeConfiguration<Payment>
{
    public PaymentConfiguration()
    {
        // One-to-One Optional
        HasOptional(s => s.Review)
            .WithOptionalDependent(s => s.Payment)
            .Map(s => s.MapKey("ReviewId"));
    }
}

Now there will be an FK field ReviewId in table Payment, and the rest of the code works without changes.

Answer 2

You must have typos in your models (2 ReviewId fields in payment?). Also, if you're going the fluent route don't put any relationship attributes in there to confuse matters. IAC, try something like this:

public class Review {
    public int ReviewId { get; set; }

    public int? PaymentId { get; set; }
    public virtual Payment Payment { get; set; }
}

public class Payment {
    public int PaymentId { get; set; }

    public int? ReviewId { get; set; }
    public virtual Review Review { get; set; }
}

public class ReviewConfiguration : EntityTypeConfiguration<Review>
{
    public ReviewConfiguration()
    {
        HasOptional(r => r.Payment)
            .WithMany()
            .HasForeignKey(r => r.PaymentId);
    }
}

public class PaymentConfiguration : EntityTypeConfiguration<Payment>
{
    public PaymentConfiguration()
    {
        HasOptional(p => p.Review)
            .WithMany()
            .HasForeignKey(p => p.ReviewId);
    }
}