c# - 连接中的 Entity Framework 子查询

Question

我在连接运算符中使用子查询时遇到问题。

我想知道如何改进我的 LINQ 查询。

我想变成这样的查询:

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

我用下面的 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();

但是这个 LINQ 查询会导致多个查询:

我尝试了很多东西。就像在这个例子中使用 let Stack Overflow answer .我最后的尝试是基于 this Stackoverflow answer

我还尝试使用 .Take(1) 而不是 .First()

如果有人能指出正确的方向，我会很高兴。真挚地，布莱希特

Answer 1

我建议使用 LINQ GroupJoin (类似于 LINQ LEFT OUTER JOIN 模拟，但不是 DefaultIfEmpty 使用 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)

当然，生成的 SQL 查询会有所不同(构造转换为 CROSS APPLY)，但结果应该是相同的。