EF CF - How do I build optional:optional relationship?

Question

I want Foo to have an optional Bar and Bar to have an optional Foo.

I seemed to manage to get it working but I had an extra column being created on only one of the tables, e.g. it made InvitationId and then also Invitation_Id in SQL on only one of the tables, even though both entities are setup the same way, but in reverse.

So I wanted to make a smaller repro so I could ask the question on SO, but in the process, and although I have just copied the original entities, removed some properties, I now have a different error, which is worryingly non-deterministic.

Ok, code.

[Table("Foo")]
public partial class Foo
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    [StringLength(128)]
    public string Name { get; set; }

    // Referential

    [ForeignKey("Bar")]
    public Guid? BarId { get; set; }

    public virtual Bar Bar { get; set; }
}

[Table("Bar")]
public partial class Bar
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    [StringLength(128)]
    public string Name { get; set; }

    // Referential

    [ForeignKey("Foo")]
    public Guid? FooId { get; set; }

    public virtual Foo Foo { get; set; }
}

And in OnModelCreating

    modelBuilder.Entity<Foo>()
        .HasOptional<Bar>(foo => foo.Bar)
        .WithOptionalPrincipal(bar => bar.Foo);

The error is:

The navigation property 'Foo' declared on type 'Product.Data.Entities.Bar' has been configured with conflicting foreign keys.

The original entities still exist in the project and are setup in exactly the same way except they have more properties, but they get created without error, except one has the extraneous FK column.

So there's a number of issues:

• Why did I get the extra Invitation_Id column when it already has InvitationId?

• Why can I not reproduce it?

• Why does the error now appear? And if I solve that, is it going to help me with my original entities if they don't have the same issue.

• What's the proper way of achieving my objective in my opening sentence above?

Meanwhile, I'm going to begin building Foo and Bar back into Invitation and Expectation bit by bit until it goes funny.

Update

So I ended up with EXACT copies of the original entities in all but name. These copies caused the FK conflict error above, but the originals do not!!

I then removed the originals and renamed the copies to their original names, changing none of the properties or attributes, and the error went away and I was back to the original issue of the extraneous FK column!

Bonkers.

Luke

Answer 1

The first thing is in an one to one relationship one end must be the principal and the another one is the dependent, so you can't specify a FK property in both entities. The second thing is if you are going to use a FK property, EF requires the PK of the dependent entity should be FK too:

public class Principal
{
   public int Id{get;set;}
   public virtual Dependent Dependent{get;set;}
}

public class Dependent
{
   [Key, ForeignKey("Principal")]
   public int PrincipalId{get;set;}

   public virtual Principal Principal{get;set;}
}

The third thing is EF lets you configure a one-to-one relationship with optional in both sides using Fluent API, but you can specify the FK, because as I said before, it should be configured as PK too, so EF will handle that FK for you in DB, that's way you have an extra Invitation_Id column.

To resolve your issue your model should be this way(remove the FK properties):

[Table("Foo")]
public partial class Foo
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    [StringLength(128)]
    public string Name { get; set; }

    // Referential
    public virtual Bar Bar { get; set; }
}

[Table("Bar")]
public partial class Bar
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    [StringLength(128)]
    public string Name { get; set; }

    // Referential
    public virtual Foo Foo { get; set; }
}

And use the same Fluent Api configuration:

 modelBuilder.Entity<Foo>()
        .HasOptional(foo => foo.Bar)
        .WithOptionalPrincipal(bar => bar.Foo);

About why the exception is not happened in your real code, I think the same as @user2697817, you should be creating two different relationships, but I can fully ensure that because I'm not seeing your real model.

A second option could be the solution that is showed by @user2697817, but in that case you are going to have two different relationships.

Answer 2

As I mentioned in my comment, because there is two relationships and it's possible to have a navigation property for each side of the relationship I think EF is having trouble distinguishing which navigation prop is part of which relationship.
I would suggest defining both relationships explicitly in your OnModelCreating

modelBuilder.Entity<Foo>().HasOptional(f => f.Bar)
                          .WithMany()
                          .HasForeignKey(f => f.BarId);

modelBuilder.Entity<Bar>().HasOptional(b => b.Foo)
                          .WithMany()
                          .HasForeignKey(b => b.FooId);