EF-ing Many-to-Many Relationships

Oh dear, that title sounds nasty.

So there are lot of primers on getting started with Microsoft’s Entity Framework in a Code-First way, and this won’t be one of them. If this disappoints you and you’re Google-impaired, here’s an official video and a related article. But a whole lot of primers only explain how to put a lot of tables next to each other in a data context, stopping short of relating those tables to each other. While this is generally good enough when you’re writing queries, it comes up short if your users are writing queries because you’ve exposed your data as a service… like an OData service. This is especially true if your “tables” are just views with no obvious referential integrity that you’re writing .NET code against after the fact. It turns out that the process to do this is strange enough that you’re unlikely to simply stumble across it, but it’s easy when you know how. So here’s how.

Step 1: Have Your Major Objects. Let’s make a table (or view) for IceCream (id int primary key) and WaffleCones (id int primary key) because we can match any ice cream to any waffle cone. Sure, why not? Note that your primary keys don’t have to be strictly primary keys (for views) so long as you’ve controlled them as if they’re primary keys.

Step 2: Association Table (or View). Now in the middle we’re going to put an IceCreamConeCompatibility (distinct ice_cream_id, waffle_cone_id) based on whatever magical business logic ensures that ice cream is compatible with waffle cones.

Step 3: The Object Decoration. Go into your object classes and add the relationship property like this:

[Table("IceCream")]
public class IceCream {
     [Key]
     public int id { get; set; }
     //Here's the part that matters:
     public virtual ICollection WaffleCones { get; set; }
}
[Table("WaffleCone")]
public class WaffleCone {
     [Key]
     public int id {get; set;}
     //Again, adding this new part:
     public virtual ICollection IceCreams { get; set; }
}

Step 4: The Data Context Code. As promised, here’s the freaky part: IceCreamConeCompatibility is not recognized as an actual object in code — but that’s okay. The relationship is contextual so we’re going to mention it to our DataContext class, like this:

public class IceCreamDataContext : DbContext {
  public IceCreamDataContext(string nameOrConnectionString) 
               : base(nameOrConnectionString) {};
  public DbSet IceCreams { get; set; }
  public DbSet WaffleCones { get; set; }
  // with me so far? Here's the freakish bit
  protected override void OnModelCreating(DbModelBuilder modelBuilder) {
    // This is how to do *-to-* with a relationship table in the middle.
    modelBuilder.Entity().
       HasMany(ic => ic.WaffleCones).WithMany(wc => wc.IceCreams).Map(map =>
       {
         map.ToTable("IceCreamConeCompatibility");
         map.MapRightKey("waffle_cone_id");
         map.MapLeftKey("ice_cream_id");
       });
  }
}

So what you can see is that we’re going into Entity Framework’s black-boxery and force-feeding it a relation when it’s called into being. Put into more-or-less human terms, this relationship is:

Each ice cream (starting on the left) has many waffle cones (moving to the right), with the waffle cones also having many ice creams, and all of this is technically described in the compatibility table which references key of the right side table (waffle codes) to its “waffle_cone_id” column and the key of its left side table (ice creams) to “ice_cream_id.”

See, it’s easy when you know how. But I’m not apologizing for the title of this post.