My, this is a strange many-to-many-to-many!
Normally one would have the following design:
- Every
Product has zero or more Parts, Every Part is used in zero or more Products (many-to-many ProductPart)
- Every
Product has zero or more Enumerations, Every Enumeration is used by zero or more Products (many-to-many ProductEnumeration)
- Every
Part has zero or more Enumerations, Every Enumeration is used in zero or more Parts (many-to-many EnumerationPart)
This would lead to three junction tables ProductPart, ProductEnumeration, and EnumerationPart
You chose for a design with only one junction table.
You do realize that in your design, that as soon as a Product has a Part, that the Product and the Part both have the same Enumeration, don't you? You can't have a product with more or other Enumerations than the Parts of the product. You can't have a Product with a Part but without Enumerations.
If you really want to limit your database to this, you'll have classes like this:
class Product
{
public int Id { get; set; }
public string Name { get; set; }
public virtual ICollection<Junction> Junctions { get; set; }
}
class Part
{
public int Id { get; set; }
public int Number { get; set; }
public string Description { get; set; }
public virtual ICollection<Junction> Junctions { get; set; }
}
class Enumeration
{
public int Id { get; set; }
public int Code { get; set; }
public virtual ICollection<Junction> Junctions { get; set; }
}
Your junction table will be like:
public class Junction
{
// foreign keys; composite primary key
public int ProductId { get; set; }
public int EnumId { get; set; }
public int PartId { get; set; }
public virtual Product Product { get; set; }
public virtual Enumeration Enumeration { get; set; }
public virtual Part Part { get; set; }
}
Be aware: In entity framework the non-virtual properties represent the
actual columns in your tables; the virtual properties represent the
relations between the tables
Your DbContext will have your four tables:
class ManyDbContext : DbContext
{
public DbSet<Product> Products { get; set; }
public DbSet<Enumeration> Enumerations { get; set; }
public DbSet<Part> Parts { get; set; }
public DbSet<Junction> Junctions {get; set;}
}
Finally in OnModelCreating you'll have to specify your design:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
var junctionConfig = modelBuilder.Entity<Junction>();
// composite primary key:
junctionConfig.HasKey(junc => new { junc.ProductId, junc.PartId, junc.EnumId);
// every junctionconfig has a mandatory foreign key to Product in ProductId
// which represent a one-to-many (one Product has many Junctions)
junctionConfig.HasRequired<Product>(junc => junc.Product)
.WithMany(product => product.Junctions)
.HasForeignKey<int>(junc => junc.ProductId);
// every junctionconfig has a mandatory foreign key to Enumerations in EnumerationId
// which represent a one-to-many (one Enumeration has many Junctions)
junctionConfig.HasRequired<Enumeration>(junc => junc.Enumeration)
.WithMany(enumeration => enumeration.Junctions)
.HasForeignKey<int>(junc => junc.EnumId);
// every junctionconfig has a mandatory foreign key to Pars in PartId
// which represent a one-to-many (one Part has many Junctions)
junctionConfig.HasRequired<Part>(junc => junc.Part)
.WithMany(part => part.Junctions)
.HasForeignKey<int>(junc => junc.PartId);
base.OnModelCreating(modelBuilder);
}
Back to your question
Given a productId, give me all EnumerationCodes, Part.Number, Part.Description records for the Product with this ProductId
When using entity framework, people tend to perform joins on foreign keys, instead of using virtual properties in the tables, making things more complicated then needed.
If you'd use the virtual properties the query would be easy and very intuitive:
var result = dbContext.Junctions
.Where(junction => junction.ProductId == productId)
.Select(junction => new
{
EnumerationCode = junction.Enumeration.EnumerationCode,
PartNumber = junction.Part.Number,
PartDescription = junction.Part.Description,
});
Entity Framework is smart enough to detect which joins are needed.
If you really want to do the join you'll have to do a join with three tables. Such a join looks horrific:
var x = dbContext.Junctions // from all junctions
.Where(junction => junction.ProductId == productId) // take only the ones with productId
// The first join:
.Join(dbContext.Parts, // Join with Parts
junction => junction.PartId, // from the Junction take the PartId,
part => part.Id, // from the Parts take the Id
(junction, part) => new // when they match make one new object
{ // containing some properties
EnumerationId = junction.EnumId,
PartNumber = part.Number,
PartDescription = part.Description,
})
// Second Join
.Join(dbContext.Enumerations, // Join with enumerations
junction => junction.EnumerationId, // from the 1st join result take EnumerationId
enumeration => enumeration.Id, // from enumerations take Id
(junction, enumeration) => new // when they match make one new object
{ // containing your desired properties
EnumerationCode = enumeration.Code,
PartNumber = junction.PartNumber,
PartDescription = junction.PartDescription,
});
You are lucky you don't want the product's Description. If you'd use the virtual properties this would be easy:
var result = dbContext.Junctions
.Where(junction => junction.ProductId == productId)
.Select(junction => new
{
Description = junction.Product.Description,
EnumerationCode = junction.Enumeration.EnumerationCode,
PartNumber = junction.Part.Number,
PartDescription = junction.Part.Description,
});
Up to you to write a join with four tables. See the difference and decide which method you want to use from now on.
