I new to EntityFrameworkCore and Asp.Net Core and I have the following problem. I have 2 tables, Diplomas and Teachers and I am trying to select the diplomas that have as TeacherName the given one. I use Join() in order to inner join the 2 tables but I keep receiving an error. How can I achieve this, or is there some other way to do it ? Using Asp.Net Core 5.0 and EntityFrameworkCore 5.0

Models

public class Diploma
{
    public Diploma(){
        this.Status = "Pending";
    }
    public int Id { get; set; }
    [Required]     
    [Display(Name = "Title")]
    public string Title { get; set; }
    [Display(Name = "Teachers")]
    public List<Teacher> Teachers { get; set; }
    [Display(Name = "Description")]
    public string Description { get; set; }
    [Display(Name = "Student Name")]
    public string StudentName { get; set; }
    public string Status { get; set; }
    [Display(Name = "FilePath")]
    public string FilePath { get; set; }
}

public class Teacher
{
    public int TeacherId { get; set; }
    [Display(Name = "TeacherName")]
    public string Name { get; set; }
    public int DiplomaId { get; set; }
    public Diploma Diploma { get; set; }
}

SQlRepository

public IEnumerable<Diploma> GetAllDiplomasForTeachers(string username)
{
    Teacher t = new Teacher();
    t.Name = username;            

    var query = context.Diplomas.Join(
        context.Teachers,
        diploma => diploma.Id,
        teacher => teacher.DiplomaId,
        (diploma, teacher) => new Diploma
        {
            Id = diploma.Id,
            Title = diploma.Title,
            Description = diploma.Description,
            StudentName = diploma.StudentName,
            Teachers = context.Teachers.ToList<Teacher>(),
            Status = diploma.Status,
            FilePath = diploma.FilePath
        }
        ).Where(a => a.Teachers.Contains(t)); //This line produces[enter image description here][1] the following error
    return query;
}

Controller

public ViewResult ShowDiplomas()
{
    var userName = User.FindFirstValue(ClaimTypes.Name);            
    var diploma = _diplomaRepository.GetAllDiplomasForTeachers(userName);                                    
    if (diploma == null)
    {
        ViewBag.ErrorMessage = $"No diploma is created yet";                
    }
    return View(diploma);
}

Database Tables

Teachers
TeacherId,Name,DiplomaId

Diplomas
Id,Title,Description,StudentName,Status,FilePath

CodePudding user response：

This query is much simpler that you have tried to create. Usually it is not needed to create JOINs if you have properly defined navigation properties.

public IEnumerable<Diploma> GetAllDiplomasForTeachers(string username)
{
    var query = context.Diplomas
        .Include(d => d.Teachers)
        .Where(d => d.Teachers.Any(t => t.Name == userName));
    return query;
}