Entity Framework 1-To-* and 1-to-1

Question

(Entity Framework 6, .NET 4, VS 2010)

I have created a small Blog project to illustrate the problem. This is a Blog that has many posts but only one of the posts act as the main post.

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Post> PostEntities { get; set; }

    public int? MainPostId { get; set; }

    [ForeignKey("MainPostId")]
    public virtual Post MainPostEntity { get; set; }  // Problem here
}

public class Post
{
    public int Id { get; set; }
    public int BlogId { get; set; }
    [ForeignKey("BlogId")]
    public virtual Blog BlogEntity { get; set; }
    public string Title { get; set; }
}

modelBuilder.Entity<Blog>()
    .HasOptional(b => b.MainPostEntity)
    .WithRequired(p => p.BlogEntity);

static void Main(string[] args)
{
    Database.SetInitializer<EFTestContext>(null);
    EFTestContext db = new EFTestContext();
    Post[] posts = db.Posts.ToArray(); // Error here
}

If I remove the navigation property public virtual Post MainPostEntity everything works as expected. However, when I add it, I get:

base {System.SystemException} = {"The ForeignKeyAttribute on property 'MainPostEntity' on type 'EFTest.Blog' is not valid. The foreign key name 'MainPostId' was not found on the dependent type 'EFTest.Post'. The Name value should be a comma separated list of foreign key property names."}

If I remove the fluent API call, I get {"Invalid column name 'Blog_Id'."}

If I change the attribute from [ForeignKey("MainPostId")] to [ForeignKey("Id")] I get the following error {"Invalid column name 'Blog_Id'."}

What am I doing wrong? How do I enable the navigation property from Blog to the Main Post?

Answer 1

The problem you are having is that you're creating two relationships between the same two tables and EF can't distinguish which relationship the navigation property BlogEntity is part of. Using the fluent api you can explicitly tell it so, the data annotations are then not needed.

modelBuilder.Entity<Blog>().HasMany(b => b.PostEntities).
                            WithRequired(p => p.BlogEntity).
                            HasForeignKey(p => p.BlogId).
                            WillCascadeOnDelete(true);

modelBuilder.Entity<Blog>().HasOptional(b => b.MainPostEntity).
                            WithMany().
                            HasForeignKey(b => b.MainPostId).
                            WillCascadeOnDelete(false);