ToList() method call throws a syntax error at runtime when ordering a list in a union

Question

return await result.Select(student => new MarkSheetsStudentByIdDto
{
    Id = student.RegId,
    FullName = student.FullName,
    AnnualMarkSheets = student.TermOne
    .Select(x => new MarkSheetDto
    {
        Rank = x.Rank
        ...
        Comments = student.Comments.Where(x => x.StudentId.Equals(student.RegId)).Select(x => x.CommentText)
    }).Union(student.TermTwo
    .Select(x => new MarkSheetDto
    {
        Rank = x.Rank
        ...
        Comments = student.Comments.Where(x => x.StudentId.Equals(student.RegId)).Select(x => x.CommentText)
    })).OrderBy(c => c.Rank).ToList()
}).ToList();

For the above example code snippet, I am getting the following error at runtime.

42601: syntax error at or near \"SELECT\"\r\n\r\nPOSITION: 5680

I used ToList() method otherwise I am getting the following error.

Collections in the final projection must be an 'IEnumerable' type such as 'List'. Consider using 'ToList' or some other mechanism to convert the 'IQueryable' or 'IOrderedEnumerable' into an 'IEnumerable'.

Can anyone please guide me on how to address this scenario?

Answer 1

Try to load data via Include and then do projection on the client-side:

var rawResult = await result
    .Include(x => x.Comments)
    .Include(x => x.TermOne)
    .Include(x => x.TermTwo)
    .Take(1)
    .ToListAsync(cancellationToken);

return rawResult.Select(student => new MarkSheetsStudentByIdDto
{
    Id = student.RegId,
    FullName = student.FullName,
    AnnualMarkSheets = student.TermOne
    .Select(x => new MarkSheetDto
    {
        Rank = x.Rank
        ...
        Comments = student.Comments.Where(x => x.StudentId.Equals(student.RegId)).Select(x => x.CommentText)
    }).Union(student.TermTwo
    .Select(x => new MarkSheetDto
    {
        Rank = x.Rank
        ...
        Comments = student.Comments.Where(x => x.StudentId.Equals(student.RegId)).Select(x => x.CommentText)
    })).OrderBy(c => c.Rank).ToList()
}).ToList();

Includes can be replaced with Select later when you experienced that not needed data is requested from database.