Using: EF 4.3.1, Visual Studio 2010, SQL CE 4.0

My understanding is that when declaring Foreign Keys with DataAnnotation in EF, it can be done either of the following ways:

Option 1-

[ForeignKey("Player1Home")]
public long? HPlayer1Id { get; set; }

public virtual Player Player1Home { get; set; }

Option 2-

public long? HPlayer1Id { get; set; }

[ForeignKey("HPlayer1Id")]
public virtual Player Player1Home { get; set; }

Problem:

When the InverseProperty DataAnnotation gets used with Option 2 an extra column gets generated in the database (Player1Home_Id) in addition to HPlayer1Id.

[Table("Matches")]
public class Match
{
    [Key]
    public long Id { get; set; }

    //-- Option 1 - THIS WORKS GREAT --//
    public long? HPlayer1Id { get; set; }
    [ForeignKey("HPlayer1Id")]
    public virtual Player Player1Home { get; set; }

    //-- Option 2 - THIS DOES NOT WORK, it generates an extra column in the database --//
    [ForeignKey("Player1Home")]
    public long? HPlayer1Id { get; set; }
    public virtual Player Player1Home { get; set; }
}

[Table("Players")]
public class Player
{
    [Key]
    public long Id { get; set; }

    [InverseProperty("Player1Home")]
    public virtual ICollection<Match> MatchesAsHome1 { get; set; }
}

Of course if I rename HPlayer1Id to Player1HomeId, then Option 2 works correctly again. But the whole purpose of the DataAnnotation is to allow explicit naming when 'Conventions' cannot automatically determine the matching property.

Removing the InverseProperty DataAnnotation on the Player class also seems to fix the issue, but unfortunately I cannot do this because my actual Match class has four Players in it, and thus I need explicit mappings.

And finally, I know I can just use Option 1, but I prefer the consistency of declaring all of my Keys (Primary and Foreign) on the Id fields rather than Foreign Keys on Navigation Properties. And technically, either way is supposed to work.

Is this just a bug in 4.3.1? In EF Code first?

Or is Mapping a ForeignKey AND an InverseProperty from two different properties to a common third property not supported?

Any information would be greatly appreciated!

Update: a second bug?

A third option should work as well (as suggested by Slauma), but causes a NullReferenceException to be thrown the first time I attempt to add an entity to the database. The database never ends up getting created, whereas Option 2 from above does not have this issue. It appears this has worked for Slauma on EF 4.1, but does not for me with EF 4.3.1. (I'm using it with SQL CE 4.0)

[Table("Matches")]
public class Match
{
    [Key]
    public long Id { get; set; }
    [ForeignKey("Player1Home")]
    public long? HPlayer1Id { get; set; }
    [InverseProperty("MatchesAsHome1")]
    public virtual Player Player1Home { get; set; }
}

[Table("Players")]
public class Player
{
    [Key]
    public long Id { get; set; }
    public virtual ICollection<Match> MatchesAsHome1 { get; set; }
}

public class MyContext : DbContext
{
    public DbSet<Match> Matches { get; set; }
    public DbSet<Player> Players { get; set; }
}

Usage:

try
{
    MyContext mc = new MyContext();
    //NullReferenceException gets thrown on the next call
    mc.Matches.Add(new Match());
    mc.SaveChanges();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

• entity-framework
• foreign-keys
• ef-code-first
• data-annotations
• entity-framework-4.3

Same behaviour in EF 4.1.

You didn't mention the option to move the InverseProperty attribute to the other side of the relationship:

[Table("Matches")]
public class Match
{
    [Key]
    public long Id { get; set; }

    [ForeignKey("Player1Home")]
    public long? HPlayer1Id { get; set; }

    [InverseProperty("MatchesAsHome1")]
    public virtual Player Player1Home { get; set; }
}

[Table("Players")]
public class Player
{
    [Key]
    public long Id { get; set; }

    public virtual ICollection<Match> MatchesAsHome1 { get; set; }
}

This worked for me and didn't create the extra column.

The behaviour of your option 2 looks like a code-first bug to me.

Edit

Confirming that changing the version from EF 4.1 to EF 4.3.1 causes a NullReferenceException with the model above. The database doesn't get created.

This was fixed in EF5 and I've confirmed it still behaves correctly in EF6.

You can see notes of investigation on this issue - https://entityframework.codeplex.com/workitem/138.