EF Code First with many to many self referencing relationship

Question

I am starting out with using the EF Code First with MVC and am a bit stumped with something. I have the following db structure (Sorry but I was not allowed to post an image unfortunately):

Table - Products
Table - RelatedProducts

1-Many on Products.ProductID -> RelatedProducts.ProductID
1-Many on Products.ProductID -> RelatedProducts.RelatedProductID

Basically I have a product that can have a series of products that are related to it. These are kept in the RelatedProducts table with the relationship defined by the ProductID and the ProductID of the related product which I have named RelatedProductID. In my code I has produced the following classes:

public class MyDBEntities : DbContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<RelatedProduct> RelatedProducts { get; set; }
}

public class Product
{
    public Guid ProductID { get; set; }
    public string Name { get; set; }
    public string Heading { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
    public Guid CategoryID { get; set; }
    public string ImageURL { get; set; }
    public string LargeImageURL { get; set; }
    public string Serves { get; set; }
    public virtual List<RelatedProduct> RelatedProducts { get; set; }
}
public class RelatedProduct
{
    public Guid ProductID { get; set; }
    public Guid RelatedProductID { get; set; }
    public virtual Product Product { get; set; }
    public virtual Product SimilarProduct { get; set; }
}

I then try to access these in code using:

myDB.Products.Include("RelatedProducts").Where(x => x.ProductID == productID).FirstOrDefault();

But I keep getting the following error:

{"Invalid column name 'ProductProductID2'.\r\nInvalid column name 'ProductProductID2'.\r\nInvalid column name 'ProductProductID'.\r\nInvalid column name 'ProductProductID1'.\r\nInvalid column name 'ProductProductID2'."}

What am I doing wrong? I basically want to get a product then iterate through the RelatedProducts and display that product info.

Answer 1

The first part of the answer is that EF4 CTP5 is not correctly mapping your POCOs to the database because it's not smart enough. If you check out the database, you get:

    CREATE TABLE RelatedProducts(
        RelatedProductID uniqueidentifier NOT NULL,
        ProductID uniqueidentifier NOT NULL,
        ProductProductID uniqueidentifier NULL,
        ProductProductID1 uniqueidentifier NULL,
        ProductProductID2 uniqueidentifier NULL,
        PRIMARY KEY CLUSTERED 
        (
            RelatedProductID ASC
        )
    ) ON [PRIMARY]  

Yuck! This needs to be fixed with some manual work. In your DbContext, you add rules like so:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .Property(p => p.ProductID)
            .HasDatabaseGenerationOption(DatabaseGenerationOption.Identity);

        modelBuilder.Entity<RelatedProduct>()
            .HasKey(rp => new { rp.ProductID, rp.RelatedProductID });

        modelBuilder.Entity<Product>()
            .HasMany(p => p.RelatedProducts)
            .WithRequired(rp => rp.Product)
            .HasForeignKey(rp => rp.ProductID)
            .WillCascadeOnDelete();

        modelBuilder.Entity<RelatedProduct>()
            .HasRequired(rp => rp.SimilarProduct)
            .WithMany()
            .HasForeignKey(rp=> rp.RelatedProductID)
            .WillCascadeOnDelete(false);

        base.OnModelCreating(modelBuilder);
    }