Migration .NET Core 2.2 to .NET Core 3.1- LINQ LEFT JOIN - Calling Take() on DefaultIfEmpty does not-CodePudding

I have following code in .NET CORE 2.2.After migrating entire project to .net core 3.1, it does not works in .NET 3.1. According to This article it says DefaultIfEmpty().Take(1) will not translate to SQL.

from students in Students.Where(e => e.StudentType == studentType)
join courseDetails in Course.Where(x => x.IsActive == true)
on students.CourseId equals courseDetails.Id into studentCourse
from courseDetails in studentCourse.DefaultIfEmpty().Take(1)
select new { students, courseDetails };

When I ran above LINQ statement I got following error.

'NavigationExpandingExpressionVisitor' 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.",

PS: As per the requirement I must use Take(1). Because for each record in left side table right side can have multiple records.from that we will take only 1 record.

can anyone share suggestion to do it without breaking the LINQ Query

CodePudding user response：

I never do that in that way, I every time use First OR Default

from students in Students.Where(e => e.StudentType == studentType)
join courseDetails in Course.Where(x => x.IsActive == true)
on students.CourseId equals courseDetails.Id into studentCourse
from courseDetails in studentCourse.FirstOrDefault()
select new { students, courseDetails };

CodePudding user response：

Try next:

var query = from students in Students.AsQueryable().Where(e => e.StudentType == studentType)
    from courseDetails in Course.AsQueryable()
        .Where(x => x.IsActive == true)
        .Where(cd => cd.Id == students.CourseId)
        .Take(1)
        .DefaultIfEmpty()        
    select new { students, courseDetails };

I don't have 3.1 installed on my machine, but 5.0 seems to translate it correctly.