How does entity framework code first define a table structure like this Structure

class A
{
    PK
    Name1
    List<ClassC>
}

class B
{
    PK
    Name2
    List<ClassC>
}

class C
{
    PK
    ClassA
    ClassB
}

CodePudding user response：

The relationship you outline in your diagram will probably not do what you think it will do.

If ParentId on TableC is meant to point to either an Id on Table A or an Id on Table B, that won't work. A matching record will have to be in both Table A and Table B.

SQL Server will allow this, and EF (At least EF6) will support this as long as ParentId is declared in the entity and set up as the FK to both relationships. Shadow properties (EF6 using Map(x => x.MapKey())) do not work as it would want to use the same name for both relationships, at least in EF6. In EF Core you'd need to test whether this is supported at all.

Take the following two examples: We have a system for a school where we have Students (Table A) and Teachers (Table B). Both Students and Teachers have one or more Addresses (Table C)

An address could belong to a Student, or it could belong to a Teacher, it would rarely belong to both a student and a teacher. So a table structure like you specified with a FK between ParentID pointing to both the Student and Teacher tables wouldn't make much sense. Parent ID 5 would have to exist in both Students and Teachers rather than one or the other, and even if it could, you wouldn't know whether an address referenced a Student or a Teacher. From a DB perspective you could add a ParentType to indicate whether a ParentId referred to a Student or a Teacher, but you would have to remove the FK on ParentId since it cannot point to both tables and expect to find just one or the other. This becomes an implied relationship and isn't very efficient and cannot be enforced with constraints. (I.e. ensure that ParentId actually points to a row in either table, or the correct table, etc.) It also cannot be mapped as a relationship in EF.

The way this could work is to combine Student and Teacher into a Person table with something like a Role identifier to tell if a Person is a Student or a Teacher. In this way the Address table could legally have a PersonId. (rather than ParentId) In EF you could still have Student and Teacher classes both mapped to a Person table via a Person base class, using Role as the discriminator. In EF this is referred to as Table-per-Hierarcy inheritance.

This works where the classes that share a relationship to a common entity (like Address) are reasonably relatable. We can have a one-to-many relationship between a base-class table and the related many-side table. It doesn't work that well if you want a Student, Teacher, and School to have one or more addresses. (I.e. a school could have campuses) A School is not a "Person", nor should it be treated as one. Even with relatable entities this can be problematic where we want several fields specific to Students vs. other fields specific to Teachers. This requires either adding a bunch of null-able fields to the Person table, or 0-1 relationships from Person to either a StudentInfo or TeacherInfo table to hold the student or teacher specific details.

Where we have unrelated entities and need a one-to-many relationship across something like a Student table, Teacher table, and a School table, all relating to one or more Address records, a better option would be to employ a many-to-many relationship.

Student

StudentAddress (StudentId, AddressId)

Teacher

TeacherAddress (TeacherId, AddressId)

School

SchoolAddress (SchoolId, AddressId)

Address

In this case a Student, Teacher, or School can have a collection of Address entities if the many-to-many tables consist of just the FKs as the composite PK and no other columns, and you are using either EF6 or EF Core 5. (I believe supports this) Earlier versions of EF Core do not support referencing through an unmapped joining table automatically so this would require declaring the collections by a mapped joining entity. (I.e. ICollection<StudentAddress>)

Address could contain references back to collections of Student / Teacher / School, though generally I wouldn't bother mapping these unless they are really necessary. As a Many-to-Many relationship this would legally allow the same address to belong to multiple students which wouldn't be allowed in a proper one-to-many.

To enforce a proper one-to-many, the Address table would be removed, and the address fields would be put into each of the StudentAddress, TeacherAddress, and SchoolAddress tables.

CodePudding user response：

Your current class structure does not agree with ERD on picture.
Relationships form the picture:
C can be connected with many A, but A can be connected with just one C (1:M)
C can be connected with many B, but B can be connected with just one C (1:M)
Structure below should work:

public class A
{
    public int Id { get; set; }
    public string Name { get; set; }

    // Fully defined relationship
    public int C_Id { get; set; }
    public C C_ojb { get; set; }
}

public class B
{
    public int Id { get; set; }
    public string Name { get; set; }

    // Fully defined relationship
    public int C_Id { get; set; }
    public C C_obj { get; set; }
}

public class C
{
    public int Id { get; set; }
    public string Name { get; set; }

    // Fully defined relationship
    public List<A> As { get; set; }
    public List<B> Bs { get; set; }
}

I recommend you to visit this pages:
https://docs.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api,fluent-api-simple-key,simple-key
https://www.learnentityframeworkcore.com/