Code First Fluent API and Navigation Properties in a Join Table

Question

I have four entities that I would like to translate into database tables via code first fluent api (I'm using a model found at databaseanswers.org), but I'm not certain as to how. The problem I'm having is that SuggestedMenuId is being migrated across two different tables in a Composite key (MenuCourse and CourseRecipeChoice).

Here's the message I'm getting:

"One or more validation errors were detected during model generation:

\tSystem.Data.Entity.Edm.EdmAssociationConstraint: : The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical."

Here's what I've tried in my EntityTypeConfiguration class and it's obviously incorrect...

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 the navigation properties and the correct syntax for fluent api syntax for the 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, SuggestedMenuId)
• CourseRecipeChoice(Id, SuggestedMenuId, MenuCourseId, RecipeId) //this is actually where I get confused because according to the model, SuggestedMenuId is a PK in SuggestedMenu and a PF in MenuCourse and CourseRecipeChoice (could this just be bad design?)
• Recipe(RecipeId)

Entwine

Could you just tell me what are the keys for each table and what are the foreign keys, I'm guessing but not sure. With there should be no bigger issues I think. – Speight

@Speight I've yet to read your response, but I did update the question to add the keys... – Entwine

Answer 1

...based on the info at hand (keys, relationships are not entirely clear),
here is the most complex scenario and should cover what you might have I 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 it all config done there, though it's 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 to test e.g. something like...

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

Speight

I'll give this a shot and report back asap. – Entwine

It worked perfectly! Thank you for helping me learn something new today! – Entwine

Done! I walked out of my office and came back in because I forgot to do that :) – Entwine