Search code examples
c#.netentity-frameworklinq

Is it impossible to use Select inside a projection in a union?


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

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

Unable to translate set operation after client projection has been applied. consider moving the set operation before the last 'select' call.

If I remove the comments it will not happen. Can anyone please guide me on how to solve this?


Solution

  • You have to do client-side post processing. EF Core can translate Eager Loading queries if it is simple. Union/Concat adds additional complexity.

    var rawResult = await result.Select(student => new 
        {
            Id = student.RegId,
            FullName = student.FullName,
            TermsOne = student.TermOne
              .Select(x => new MarkSheetDto
              {
                  ...
                  Comments = student.Comments.Where(x => x.StudentId.Equals(student.RegId)).Select(x => x.CommentText).ToList()
              }).ToList(),
            TermsTwo = student.TermTwo
              .Select(x => new MarkSheetDto
              {
                  ...
                  Comments = student.Comments.Where(x => x.StudentId.Equals(student.RegId)).Select(x => x.CommentText).ToList()
              }).ToList()
        }).FirstOrDefaultAsync(cancellationToken);
    
    if (rawResult == null)
      return null;
    
    return new MarkSheetsStudentByIdDto
    {
        Id = rawResult.Id,
        FullName = rawResult.FullName,
        AnnualMarkSheets = rawResult.TermsOne.Concat(rawResult.TermsTwo).ToList()
    };