How do I use Join instead of Include to query the data in EF Core?

Question

I have Two Tabel. Just like Teachers And Students. They have a 1 to many relationship

The Two Class Like:

public class TTeachers
{  
   [Key]
   public long Id {get; set;}
   public string? No { get; set; }
   public string? Name { get; set; }
}
public class TTStudent
{
   [Key]
   public long Id {get; set;}
   public string? TeacherNo{ get; set; }
   public string? Name{ get; set; }
}

For some reason I didn't specify their foreign key.

But The student table has one field, for example teacherNo.Specifies their relationship

Now I want to use Linq to query Teacher And Student If I use foreign key. I just use Include to query Student. for example:

var data = (from t in _context.TTeachers.Include(s=> t.Students)
            where t.ID == ID 
            selet new {
             t.ID ,
             t.Name,
             t.Students,
            }).First();

So What's the equivalent use Join ? I try to this:

var data = from t in _context.TTeachers
           join s in _context.TTStudent on t.No equals s.TeacherNo into student
           where t.ID == ID
           select new { t.Name, list = students }

It will run with an error.

How can I use Join to query Teacher info and Associated student data？

I mean no navigation properties.My team doesn't allow us to use foreign keys.

Answer 1

GroupJoin has limitations with EF Core, better to do that via subquery:

var data = 
    from t in _context.TTeachers
    where a.Fid == lFid
    select new 
    { 
        t.name,
        list = _context.TTStudent.Where(s => s.TeacherNo == t.No) 
    }

Anyway, better to use navigation properties, it has less risk that you have defined wrong compare keys.