EF code First with many, many relationships for self-binding

Question

I start by using EF Code First with MVC and am a bit confused about something. I have the following db structure (sorry, but I was not allowed to post the image, unfortunately):

Table - Products
Table - Related Products

1-Lots on Products.ProductID → RelatedProducts.ProductID
1-Lots on Products.ProductID → RelatedProducts.RelatedProductID

Basically, I have a product that can have a number of products related to it. They are stored in the RelatedProducts table with the relationship defined by ProductID and ProductID of the corresponding product, which I named RelatedProductID. In my code, I created 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; } } 

Then I will try to access them in the 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 the product, then iterate through the Related Products and display this product information.

Answer 1

The first part of the answer is that EF4 CTP5 does not correctly map your POCO to the database because it is not smart enough. If you go to 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] 

Ugh! This needs to be fixed using manual work. In your DbContext you add the following rules:

  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); }