Code First Fluent API and Connection Table Navigation Properties

Question

I have four objects that I would like to translate into database tables using code that first speaks api fluently (I use the model found in databaseanswers.org), but I'm not sure how to do this. The problem I am encountering is that the InviteMenuId method wraps itself through two different tables in a composite key (MenuCourse and CourseRecipeChoice).

Here is the message I get:

"During model generation, one or more validation errors were detected:

\ tSystem.Data.Entity.Edm.EdmAssociationConstraint :: The number of properties in the dependent and primary roles in the relationship constraint must be identical.

Here is what I tried in my EntityTypeConfiguration class, and this is clearly wrong ...

public class CourseRecipeChoiceConfiguration : EntityTypeConfiguration<CourseRecipeChoice> { public CourseRecipeChoiceConfiguration() { HasKey(crc => new { crc.Id}); HasRequired(r => r.Recipe).WithMany(crc => crc.CourseRecipeChoices).HasForeignKey(crc => crc.RecipeId); HasRequired(m => m.MenuCourse).WithMany(crc => crc.CourseRecipeChoices).HasForeignKey(crc => crc.MenuCourseId); HasRequired(m => m.MenuCourse).WithMany(crc => crc.CourseRecipeChoices).HasForeignKey(crc => crc.SuggestedMenu_MenuCourseId); } } 

What is the correct syntax for navigation properties and the correct syntax for smooth api syntax for a CourseRecipeChoice join table?

 public class SuggestedMenu { public int SuggestedMenuId { get; set; } public virtual ICollection<MenuCourse> MenuCourses { get; set; } } public class MenuCourse { public int Id { get; set; } public int SuggestedMenuId { get; set; } public SuggestedMenu SuggestedMenu { get; set; } public virtual ICollection<CourseRecipeChoice> CourseRecipeChoices { get; set; } } public class CourseRecipeChoice { public int SuggestedMenuId { get; set; } public int MenuCourseId { get; set; } public int Id { get; set; } public int RecipeId { get; set; } //How do I represent the navigation properties in this class? } public class Recipe { public int RecipeId { get; set; } public string Name { get; set; } public string Description { get; set; } public ICollection<CourseRecipeChoice> CourseRecipeChoices { get; set; } } 

The keys are as follows:

• SuggestedMenu (Id),
• MenuCourse (Id, RecommendedMenuId)
• CourseRecipeChoice (Id, RecommendedMenuId, MenuCourseId, RecipeId) // this is actually where I got confused, because according to the model, the proposed MünuId is PK in the PresMenu project and PF in MenuCourse and CourseRecipeChoice (maybe this is just a bad design?)
• Recipe (RecipeID)

Answer 1

... based on information (keys, relationships are not entirely clear),
here is the most difficult scenario and should cover what you might think ...

 public class SuggestedMenu { public int SuggestedMenuId { get; set; } public string Description { get; set; } public virtual ICollection<MenuCourse> MenuCourses { get; set; } // public virtual ICollection<CourseRecipeChoice> CourseRecipeChoices { get; set; } } public class MenuCourse { public int MenuCourseId { get; set; } public int SuggestedMenuId { get; set; } public SuggestedMenu SuggestedMenu { get; set; } public virtual ICollection<CourseRecipeChoice> CourseRecipeChoices { get; set; } } public class CourseRecipeChoice { public int CourseRecipeChoiceId { get; set; } public int MenuCourseId { get; set; } public int SuggestedMenuId { get; set; } public int RecipeId { get; set; } // no virtuals if required, non-optional public Recipe Recipe { get; set; } public MenuCourse MenuCourse { get; set; } // public SuggestedMenu SuggestedMenu { get; set; } } public class Recipe { public int RecipeId { get; set; } public string Name { get; set; } public string Description { get; set; } public virtual ICollection<CourseRecipeChoice> CourseRecipeChoices { get; set; } } 

... and in OnModelCreating (I prefer the whole configuration to be done there, although this is the same) ...

 modelBuilder.Entity<CourseRecipeChoice>() .HasKey(crc => new { crc.CourseRecipeChoiceId, crc.SuggestedMenuId, crc.MenuCourseId, crc.RecipeId }); modelBuilder.Entity<CourseRecipeChoice>() .HasRequired(r => r.Recipe) .WithMany(crc => crc.CourseRecipeChoices) .HasForeignKey(crc => crc.RecipeId) .WillCascadeOnDelete(false); modelBuilder.Entity<CourseRecipeChoice>() .HasRequired(m => m.MenuCourse) .WithMany(crc => crc.CourseRecipeChoices) .HasForeignKey(crc => new { crc.MenuCourseId, crc.SuggestedMenuId }) .WillCascadeOnDelete(false); modelBuilder.Entity<SuggestedMenu>() .HasKey(crc => crc.SuggestedMenuId ); modelBuilder.Entity<MenuCourse>() .HasKey(crc => new { crc.MenuCourseId, crc.SuggestedMenuId }); modelBuilder.Entity<MenuCourse>() .HasRequired(m => m.SuggestedMenu) .WithMany(crc => crc.MenuCourses) .HasForeignKey(crc => crc.SuggestedMenuId) .WillCascadeOnDelete(false); modelBuilder.Entity<Recipe>() .HasKey(crc => crc.RecipeId ); 

... and for verification, for example. sort of...

  using (var db = new YourDbContext()) { SuggestedMenu suggestedmenu = new SuggestedMenu { Description = "suggested menu" }; var menucourse = new MenuCourse { MenuCourseId = 2, SuggestedMenu = suggestedmenu }; var recipe = new Recipe { Name = "My recipe", Description = "recipe desc" }; var crc = new CourseRecipeChoice { CourseRecipeChoiceId = 2, MenuCourse = menucourse, Recipe = recipe, }; db.CourseRecipeChoices.Add(crc); int recordsAffected = db.SaveChanges(); foreach (var crcs in db.CourseRecipeChoices.Include(c => c.MenuCourse).Include(c => c.Recipe)) { Console.WriteLine("{0}, {1}, {2}, {3}", crcs.MenuCourse.MenuCourseId, crcs.MenuCourse.SuggestedMenuId, crcs.Recipe.Name, crcs.Recipe.Description); } }