EF6 Code First Entity with navigation property collection of same type - how do I tell EF what the relationship is?

c# entity-framework entity-framework-6 navigation-properties relationships

Question

Please be patient with me while I struggle through my ignorance and confusion because I don't completely comprehend EF. One limitation I have is that the database structure cannot be changed.

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'd like to represent this using EF code first, but I'm having trouble because I don't know how EF functions. However, I can make it work using EF code generation. I'll just have to accept EF code gen if I can't make it to work with code first. I'm hoping someone can help me see clearly.

ENTITY

[Table("TBLGRADES")]
public class Grade
{
    public GUID GradeId { get; set; }
    public string GradeTitle { get; set; }
    public virtual ICollection<Grade> EligibleGrades { get; set; }
}

To have a collection of Grade entities (EligibleGrades) (count: 2) with 1 instance of not so cool grade and 1 instance of lame grade in accordance with TBLGRADESRELATIONSHIPS, I only need one instance of Grade entity representing Cool Grade.

var grade = rep.GetGradeByID(Guid.Parse("882349d4-2564-4160-a034-2a5116dec389"));
grade.EligibleGrades[0] //Not so cool grade
grade.EligibleGrades[1] //lame grade

Please explain associations to me so I can get that going. I'm grateful. This is my first post on SO, so please be kind.

1
1
1/20/2014 3:20:32 PM

Accepted Answer

It's a many-to-many relationship that uses self-reference. A grade may be the eligible grade for many other grades as well as have numerous eligible grades of its own. You can consider it to be as if theGrade entity had a second (secret) collection, such as:

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

We simply removed the collection once more because the name is so terrible. However, you must still inform EF that the relationship is one of many to many. The Code-First model discovery always assumes a one-to-many connection (with a foreign key in place) if it only detects one collection.TBLGRADES table that is not real). To construct a many-to-many relationship instead, you must use Fluent API to modify the default conventions:

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

Your last piece of code should now function with this mapping and produce the exact outcome you were hoping for.

0
1/20/2014 6:52:56 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow