I dont quite understand EF so please bear with me as I try to trudge through my ignorance and confusion. I have one restriction, I cannot change the database structure.
TBLGRADES
GRADEID GUID (PK) | GRADETITLE VARCHAR
--------------------------------------------------------------
882349d4-2564-4160-a034-2a5116dec389 | Cool Grade
59539804-5c47-46ac-873d-65b33ce6ac94 | Not so cool grade
b00d6cdd-3273-4f83-8d18-0c9e9a3e1562 | Lame Grade
TBLGRADESRELATIONSHIPS
GRADEID GUID (FK) | ELIGIBLEGRADEID GUID (FK)
----------------------------------------------------------------------------
882349d4-2564-4160-a034-2a5116dec389 | 59539804-5c47-46ac-873d-65b33ce6ac94
882349d4-2564-4160-a034-2a5116dec389 | b00d6cdd-3273-4f83-8d18-0c9e9a3e1562
I would like this represented using EF code first and I'm struggling as I don't understand how EF works but I can get it to work using EF code generation. If I cant get it to work using code first, then I'll just have to settle for EF code gen. Hopefully someone can clear the clouds in my head.
ENTITY
[Table("TBLGRADES")]
public class Grade
{
public GUID GradeId { get; set; }
public string GradeTitle { get; set; }
public virtual ICollection<Grade> EligibleGrades { get; set; }
}
I would just like an instance of Grade entity representing Cool Grade, to have a collection of Grade entities (EligibleGrades) (count: 2) with 1 instance of not so cool grade and 1 instance of lame grade as per TBLGRADESRELATIONSHIPS.
var grade = rep.GetGradeByID(Guid.Parse("882349d4-2564-4160-a034-2a5116dec389"));
grade.EligibleGrades[0] //Not so cool grade
grade.EligibleGrades[1] //lame grade
Please help me understand associations so I can get that working. Appreciate it. My first SO posting so please be gentle.
It's a (self-referencing) many-to-many relationship: A grade can have many eligible grades and a grade can be the eligible grade for many other grades. You can think of it as if the Grade
entity had another (hidden) collection, like:
public class Grade
{
public GUID GradeId { get; set; }
public string GradeTitle { get; set; }
public virtual ICollection<Grade> EligibleGrades { get; set; }
public virtual ICollection<Grade> GradesThisIsAnEligibleGradeFor { get; set; }
}
The name is so ugly, we just remove the collection again. But you still have to tell EF that the relationship is many-to-many. If the Code-First model discovery sees only one collection it always assumes a one-to-many relationship (with a foreign key in TBLGRADES
table that doesn't exist). You must override the default conventions by using Fluent API in order to create a many-to-many relationship instead:
modelBuilder.Entity<Grade>()
.HasMany(g => g.EligibleGrades)
.WithMany() // <- parameterless because there's no 2nd (inverse) collection
.Map(m =>
{
m.ToTable("TBLGRADESRELATIONSHIPS");
m.MapLeftKey("GRADEID");
m.MapRightKey("ELIGIBLEGRADEID");
});
The last code snippet in your question should work now with this mapping and give exactly the result you expect.