Entity Framework: Many-to-one Association with self referencing

Question

I have some problem creating the association with Entity Framework Code First.

In this problem, i have a class - lets call it ClassA which looks like

public class ClassA
{
     [Key]
     public int ClassAId { get; set; }

     public string Name { get; set; }

     [ForeignKey("FromClassAId")]
     public virtual List<Association> Associations { get; set; }
}

There i have a association Table (because i have alot of associations and don't want to create douzends of associations table). In this association Table i have a reference back to ClassA. Then Association-Class looks like

public class Association
{
     [Key]
     public int AssociationId { get; set; }

     public int FromClassAId { get; set; }

     public int ClassAId { get; set; }

     public ClassA ClassA { get; set; }
}

So, from ClassA there is a reference to Association over ClassA:ClassAId -> Association:FromClassAId and then a reference from Association:ClassA(ClassAId) -> ClassA:ClassAId

So, following data in the database

ClassA

ClassAId     Name
-------------------------------------------------------------------------
1            Test Data 1
2            Test Data 2
3            Test Data 3

Association

AssociationId     FromClassAId    ClassAId
---------------------------------------------------------------------------
1                 1               2
1                 1               3

Should return following object (with .Include("Associations").Include("Associations.ClassA") (just json format for better seeing :) )

{
  ClassAId: 1,
  Name: 'Test Data 1',
  Associations: 
  [
    {
      FromClassAId: 1,
      ClassAId: 2,
      ClassA: 
      {
        ClassAId: 2,
        Name: 'Test Data 2'
      }
    },
    {
      FromClassAId: 1,
      ClassAId: 3,
      ClassA: 
      {
        ClassAId: 3,
        Name: 'Test Data 3'
      }
    }
  ]
}

With the ForeignKey set like above, i get the association-reference, but not the "self"-referencing back to the ClassA Object from Association. I tried several configurations, but i won't get the ClassA Reference successfully back. Maybe i make a brain-bug :)

Thanks

SOLUTION

I found the solution, after debuging and using the SQL Profiler, maybe someone has the same problem.

public class ClassA
{
     [Key]
     public int ClassAId { get; set; }

     public string Name { get; set; }

     [ForeignKey("ClassAId")]
     public virtual List<Association> Associations { get; set; }
}

public class Association
{
     [Key]
     public int AssociationId { get; set; }

     public int ClassAId { get; set; }

     public int ToClassAId { get; set; }

     [ForeignKey("ToClassAId")]
     public ClassA ClassA { get; set; }
}

Maybe EF has some problems when using two FK together, where only the back reference in the child class is named like the primary key in the mother class.

Answer 1

In code-first, you as a developer shouldn't worry about how the database will reflect your data model, just create C# classes that make sense for your data/domain model (in most cases anyways). This is the philosophical difference behind object-relational-mappers like EF-Code first. You play the role of software developer, and let EF-Code First be the DBA (for the basics at least).

Instead of an "Association" type and worrying about the table, just have a list of the same object.

public class ClassA
{
     [Key]
     public int ClassAId { get; set; }

     public string Name { get; set; }

     public virtual List<ClassA> Associations { get; set; }
}

…Then your eager loading using .Include("Associations") will work. The "FromClassAId" becomes implied by the fact that you find an instance of ClassA in some other instance's Association property.

Leave it to Entity Framework to create a sql schema that works for adds, deletes, etc… If the schema that EF creates is not to your liking, then you can start adding attributes, fluent api modifications, etc.