Entity Framework subquery in join

Question

I have a problem using a subquery inside my join operator.

I'd like to know how I can make my LINQ query better.

I want to become a query like this:

SELECT Submissions.Title, SubmissionStatusEvents.ToStatus, SubmissionStatusEvents.ToStatusId, SubmissionStatusEvents.Created, SubmissionComments.Created, Content =
         CASE 
              WHEN SubmissionComments.Type = '1'
                 THEN SubmissionComments.Content
              ELSE NULL
         END, AspNetUsers.UserName, AspNetUsers.AvatarId , Projects.Name, Comapnies.LogoId
FROM Submissions
JOIN SubmissionComments ON SubmissionComments.Id =
    (
        select TOP 1 Id
        From SubmissionComments
        where SubmissionComments.SubmissionId = Submissions.Id
        Order by SubmissionComments.Created desc
    )
JOIN SubmissionStatusEvents ON SubmissionStatusEvents.Id =
    (
         select TOP 1 Id
         From SubmissionStatusEvents
         where SubmissionStatusEvents.SubmissionId = Submissions.Id
         Order by SubmissionStatusEvents.Created desc
    )
JOIN AspNetUsers ON SubmissionComments.CommenterId=AspNetUsers.Id
JOIN Projects ON  Projects.Id = Submissions.ProjectId
JOIN Companies ON Projects.CompanyId = Companies.ID

I tried it with following LINQ:

(from submission in _ctx.Submissions
 join status in _ctx.SubmissionStatusEvents on (from s in _ctx.SubmissionStatusEvents where s.IsPublic && s.SubmissionId == submission.Id orderby s.Created descending select s.Id).First() equals status.Id
 join comment in _ctx.SubmissionComments on (from c in _ctx.SubmissionComments where c.IsPublic && c.SubmissionId == submission.Id orderby c.Created descending select c.Id).First() equals comment.Id
 join user in _ctx.Users on comment.CommenterId equals user.Id
 join project in _ctx.Projects on submission.ProjectId equals project.Id
 join company in _ctx.Companies on project.CompanyId equals company.Id
 where submission.SubmitterId == userId
 where status.IsPublic
 select new SubmissionWithLastEventChangeDto
 {
     Id = submission.Id,
     Title = submission.Ttile,
     Status = status.ToStatus,
     StatusId = status.ToStatusId,
     StatusChange = status.Created,
     ProjectId = project.Id,
     ProjectName = project.Name,
     ProjectType = project.Type,
     MaxPayout = project.ExceptionalPayout ?? project.CriticalPayout,
     LogoId = company.LogoId,
     LastComment = new LastEventChangeDto
     {
         UserName = user.UserName,
         AvatarId = user.AvatarId,
         Created = comment.Created,
         Type = comment.Type,
         Content = comment.Type == EntityEnum.SubmissionCommentType.Event ? comment.Content : null
     }
 }).ToListAsync();

However this LINQ query causes multiple queries:

I tried many things. Like using let as in this example Stack Overflow answer. My final attempt is based on this Stackoverflow answer

I also tried to use .Take(1) instead of .First()

If someone could point me in the correct direction I would be happy. Sincerely, Brecht

Answer 1

I would suggest utilizing the LINQ GroupJoin (similar to LINQ LEFT OUTER JOIN emulation, but instead of DefaultIfEmpty use OrderByDescending + Take):

(from submission in db.Submissions
 join status in _ctx.SubmissionStatusEvents on submission.Id equals status.SubmissionId into statusGroup
 from status in statusGroup.OrderByDescending(status => status.Created).Take(1)
 join comment in _ctx.SubmissionComments on submission.Id equals comment.SubmissionId into commentGroup
 from comment in commentGroup.OrderByDescending(comment => comment.Created).Take(1)
... the rest (no change)

Of course the generated SQL query will be different (the construct translates to CROSS APPLY) but the result should be the same.

Answer 2

Your query feels a little odd (may be to do with the structure), but I would be tempted to try something like this:

(from submission in _ctx.Submissions
 where submission.SubmitterId == userId
 select new SubmissionWithLastEventChangeDto
 {
     Id = submission.Id,
     Title = submission.Ttile,
     Status = submission.SubmissionStatusEvents.OrderByDescending(e => e.Created).First().ToStatus,
     StatusId = submission.SubmissionStatusEvents.OrderByDescending(e => e.Created).First().ToStatusId,
     StatusChange = submission.SubmissionStatusEvents.OrderByDescending(e => e.Created).First().Status,,
     ProjectId = submission.Project.ProjectId,
     ProjectName = submission.Project.Name,
     ProjectType = submission.Project.Type,
     MaxPayout = submission.Project.ExceptionalPayout ?? submission.Project.CriticalPayout,
     LogoId = submission.Project.Company.LogoId,
     LastComment = new LastEventChangeDto(submission.SubmissionComments.OrderByDescending(e => e.Created).First())

 }).ToListAsync();

 //CTOR for this class
 public  LastEventChangeDto(SubmissionComment comment)
 {
          UserName = comment.User.UserName,
         AvatarId = comment.User.AvatarId,
         Created = comment.Created,
         Type = comment.Type,
         Content = comment.Type == EntityEnum.SubmissionCommentType.Event ? comment.Content : null
}

Might be a bit easier to follow than all the many joins in linq.

I might also suggest a Dto to pass through your flatted status fields as well - could take a SubmissionStatus as a parameter.