What's wrong with my translation from Linq Query Expression to Method-Based Query?

Question

I have two Aggregate Roots: Student and Test, and each student could have 0..1 test. I am using EF Core 3.1 to perform a left outer join query to fetch students with her own test. Since the test could be null for a student, and EF Core dosen't support database GroupJoin, I write down my query as this:

    var studentsWithTest = from o in dbContext.Students
        join x in dbContext.Tests on o.TestId
            equals x.Id into tests
        from x in tests.DefaultIfEmpty()
        select new {o.Student, Test = x};

It works. However, what I am really interested is the equivalent method-based query performing the same fuction. I try to translate it as this:

    var studentsWithTest = dbContext.Students.SelectMany(o =>
        dbContext.Tests
            .Where(x => x.Id == o.TestId).DefaultIfEmpty()
            .Select(x => new {o.Student, Test = x}));

But this code causes run-time Exception:

Processing of the LINQ expression '(ProjectionBindingExpression: Student)' by 'RelationalProjectionBindingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

Dose that mean I have made something wrong during the translation? Could you guys help me to figure that out? Thanks.

Answer 1

For this error, the link in the error message will be the best explanation.

To solve this , add ToList() to dbContext.Students as follow :

   var studentsWithTest = dbContext.Students.ToList().SelectMany(o =>
        dbContext.Tests
            .Where(x => x.Id == o.TestId).DefaultIfEmpty()
            .Select(x => new {o.Student, Test = x}));

However, for the one-to-one relationship in ef core,you can also use Include is actually the easiest way, please refer to the following writing:

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

    public int? TestId{ get; set; }
    public Test Test{ get; set; }
}

public class Test
{
    public int Id{ get; set; }
    public string Name { get; set; } 
    public Student Student { get; set; }
}

Linq:

    var studentsWithTest = dbContext.Students.Include(x => x.Tests)
                           .Select(x => new { x, Test = x.Tests });

---

Update

As you comment said, you can try this code:

var  studentsWithTest = dbContext.Students
                  .SelectMany(o =>  dbContext.Tests.Where(x => x.Id == o.TestId).DefaultIfEmpty(), (o, x) => new
                  {
                      o.Student,
                      Test = x
                  });

You can also refer to this.