Reference to multiple rows entity-framework?

How can I reference my column in table1 to multiple rows of another table in entity-framework work with custom navigation properties?

this problem can be solved in SQL with a temp table which is proposed in How can you reference multiple rows in another table?

Answer

If one row of a database table relates to several rows in another table of the same database, then we say that there is a one-to-many relation between those two tables.

For example: a table with Schools and a table with Students. Every School in the table of Schools has zero or more Students from the table of Students.

In a relational database a one-to-many relation is implemented by adding an extra column to the many table: a foreign key to the one table.

In the School-Student database, we would add a foreign key to the Student table, which would point to the School that this Student belongs to. So all Students of the School with primary key 4, would have the same value for the foreign key: 4

If you follow the entity framework code first coding conventions, you would have something similar to:

class School
{
    public int Id {get; set;}          // will become the primary key
    public string Name {get; set;}
    ... // other properties

    // every School has zero or more Students (one-to-many)
    public virtual ICollection<Student> Students {get; set;}
}

class Student
{
    public int Id {get; set;}          // will become the primary key
    public string Name {get; set;}
    public DateTime BirthDate {get; set;}
    ... // other properties

    // every Student belongs to exactly one School, using foreign key:
    public int SchoolId {get; set;}
    public virtual School School {get; set;}
}

The DbContext:

class SchoolContext : DbContext
{
    public DbSet<School> Schools {get; set;}
    public DbSet<Student> Students {get; set;}
}

This is all that entity framework needs to identify your tables, the columns of the tables and the relation between the tables. Only if you want to use different identifiers, or non-default behaviour, you’ll need to add attributes or use fluent API.

In entity framework the columns of the tables are represented by non-virtual properties. The virtual properties represent the relations between the tables (one-to-many, many-to-many, …)

People with a database background tend to think in SQL statements which they translate into LINQ. However, the use of the virtual properties simplifies the queries

Give me the names of all Students together with the name of the school they attend.

var result = dbContext.Students.Join(dbContext.Schools, // join students and schools
    student => student.SchoolId,                        // from Student take the SchoolId,
    school => school.Id,                                // from School take the Id,
    (student, school) => new                            // when they match, make a new object
    {
        StudentName = student.Name,
        SchoolName = school.Name,
    });

Using the virtual properties makes life easier:

var result = dbContext.Students.Select(student => new
{
     StudentName = student.Name,
     SchoolName = student.School.Name,
});

In words: from the collection of Students, select from every Student his name and the name of his (one-and-only) School

Because I used the virtual properties, the code looks much simpler. Entity framework knows the relation between the tables and knows that an inner join is needed for this.

Give me the names of all Schools with the names of their students

var schoolsWithTheirStudents = dbContext.Schools.Select(school => new
{
     Name = school.Name,
     StudentNames = school.Students.Select(student => student.Name).ToList(),
});

In words: from the collection of Schools, select from every School its names, and from every Student in this school his name.

If you have a one-to-many in entity framework and you want to do an inner join, start with the many side (Students). If you want to do a GroupJoin, start with the one side (Schools)