LINQ多连接查询问题：添加Images计数时遇转换错误或计数失效

现有一段LINQ查询代码，通过GroupJoin和SelectMany实现Posts与Users的左连接，并统计每篇Post的Comments数量。现在需要新增关联_context.Images，实现和Comments类似的计数功能。尝试两种写法后均出现问题：

• 第一种写法出现EF Core无法翻译的错误；
• 第二种写法能正常统计Images数量，但Comments计数失效。
  需要获取正确的实现方案。

初始查询代码 #

public IQueryable<PostView> PostJoinQuery()
{
    return _context.Posts.GroupJoin(
        _context.Users,
        post => post.User_Id,
        user => user.User_Id,
        (posts, users) => new { posts, users })
        .SelectMany(
        x => x.users.DefaultIfEmpty(),
        (post, user) => new PostView
        {
            User_Id = post.posts.User_Id,
            Post_Id = post.posts.Post_Id,
            Author = user.Username,
            First_Name = user.First_Name,
            Last_Name = user.Last_Name,
            Created = post.posts.Created,
            Modified = post.posts.Modified,
            Content = post.posts.Content,
            Profile_Picture = user != null ? user.Profile_Picture : "No picture",
        })
        .GroupJoin(
        _context.Comments,
        post => post.Post_Id,
        comment => comment.Post_Id,
        (post, comment) => new
        {
            PostView = post,
            Comments = comment.Count()
        })
        .Select(result => new PostView
        {
            User_Id = result.PostView.User_Id,
            Post_Id = result.PostView.Post_Id,
            Author = result.PostView.Author,
            First_Name = result.PostView.First_Name,
            Last_Name = result.PostView.Last_Name,
            Created = result.PostView.Created,
            Modified = result.PostView.Modified,
            Content = result.PostView.Content,
            Profile_Picture = result != null ? result.PostView.Profile_Picture : "No picture",
            Comments = result.Comments
        });
}

第一种尝试代码（EF Core无法翻译） #

public IQueryable<PostView> PostJoinQuery()
{
    return _context.Posts.GroupJoin(
        _context.Users,
        post => post.User_Id,
        user => user.User_Id,
        (posts, users) => new { posts, users })
        .SelectMany(
        x => x.users.DefaultIfEmpty(),
        (post, user) => new PostView
        {
            User_Id = post.posts.User_Id,
            Post_Id = post.posts.Post_Id,
            Author = user.Username,
            First_Name = user.First_Name,
            Last_Name = user.Last_Name,
            Created = post.posts.Created,
            Modified = post.posts.Modified,
            Content = post.posts.Content,
            Profile_Picture = user != null ? user.Profile_Picture : "No picture",
        })
        .GroupJoin(
        _context.Comments,
        post => post.Post_Id,
        comment => comment.Post_Id,
        (post, comment) => new
        {
            PostView = post,
            Comments = comment.Count()
        })
        .GroupJoin(
        _context.Images,
        post => post.PostView.Post_Id,
        image => image.Post_Id,
        (post, image) => new
        {
            PostView = post,
            Images = image.Count()
        })
        .Select(result => new PostView
        {
            User_Id = result.PostView.PostView.User_Id,
            Post_Id = result.PostView.PostView.Post_Id,
            Author = result.PostView.PostView.Author,
            First_Name = result.PostView.PostView.First_Name,
            Last_Name = result.PostView.PostView.Last_Name,
            Created = result.PostView.PostView.Created,
            Modified = result.PostView.PostView.Modified,
            Content = result.PostView.PostView.Content,
            Profile_Picture = result != null ? result.PostView.PostView.Profile_Picture : "No picture",
            Comments = result.PostView.Comments,
            Images = result.Images
        });
}

错误信息：

could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'

第二种尝试代码（Images计数正常，Comments计数失效） #

public IQueryable<PostView> PostJoinQuery()
{
    return _context.Posts.GroupJoin(
        _context.Users,
        post => post.User_Id,
        user => user.User_Id,
        (posts, users) => new { posts, users })
        .SelectMany(
        x => x.users.DefaultIfEmpty(),
        (post, user) => new PostView
        {
            User_Id = post.posts.User_Id,
            Post_Id = post.posts.Post_Id,
            Author = user.Username,
            First_Name = user.First_Name,
            Last_Name = user.Last_Name,
            Created = post.posts.Created,
            Modified = post.posts.Modified,
            Content = post.posts.Content,
            Profile_Picture = user != null ? user.Profile_Picture : "No picture",
        })
        .GroupJoin(
        _context.Comments,
        post => post.Post_Id,
        comment => comment.Post_Id,
        (post, comments) => new
        {
            post,
            comments
        })
        .SelectMany(
        x => x.comments.DefaultIfEmpty(),
        (post, comments) => new PostView
        {
            User_Id = post.post.User_Id,
            Post_Id = post.post.Post_Id,
            Author = post.post.Author,
            First_Name = post.post.First_Name,
            Last_Name = post.post.Last_Name,
            Created = post.post.Created,
            Modified = post.post.Modified,
            Content = post.post.Content,
            Profile_Picture = post.post != null ? post.post.Profile_Picture : "No picture",
            //Comments = post.comments.Count() // *** THIS FAILS
        })
        .GroupJoin(
        _context.Images,
        post => post.Post_Id,
        image => image.Post_Id,
        (post, image) => new
        {
            PostView = post,
            Images = image.Count()
        })
        .Select(result => new PostView
        {
            User_Id = result.PostView.User_Id,
            Post_Id = result.PostView.Post_Id,
            Author = result.PostView.Author,
            First_Name = result.PostView.First_Name,
            Last_Name = result.PostView.Last_Name,
            Created = result.PostView.Created,
            Modified = result.PostView.Modified,
            Content = result.PostView.Content,
            Profile_Picture = result != null ? result.PostView.Profile_Picture : "No picture",
            Comments = result.PostView.Comments,
            Images = result.Images
        });
}

正确实现方案 #

方案1：子查询统计（简洁高效） #

这种写法直观易懂，EF Core能直接翻译为高效的SQL：

public IQueryable<PostView> PostJoinQuery()
{
    return from post in _context.Posts
           join user in _context.Users on post.User_Id equals user.User_Id into userGroup
           from user in userGroup.DefaultIfEmpty()
           select new PostView
           {
               User_Id = post.User_Id,
               Post_Id = post.Post_Id,
               Author = user?.Username,
               First_Name = user?.First_Name,
               Last_Name = user?.Last_Name,
               Created = post.Created,
               Modified = post.Modified,
               Content = post.Content,
               Profile_Picture = user != null ? user.Profile_Picture : "No picture",
               Comments = _context.Comments.Count(c => c.Post_Id == post.Post_Id),
               Images = _context.Images.Count(i => i.Post_Id == post.Post_Id)
           };
}

方案2：预统计关联表后再关联 #

如果坚持使用GroupJoin风格，可以先预统计评论和图片的数量，再与主查询结果关联：

public IQueryable<PostView> PostJoinQuery()
{
    // 预统计每个Post的评论数
    var commentCounts = _context.Comments
        .GroupBy(c => c.Post_Id)
        .Select(g => new { Post_Id = g.Key, Count = g.Count() });

    // 预统计每个Post的图片数
    var imageCounts = _context.Images
        .GroupBy(i => i.Post_Id)
        .Select(g => new { Post_Id = g.Key, Count = g.Count() });

    // 先关联Post和User
    var postWithUser = _context.Posts
        .GroupJoin(_context.Users, 
                   p => p.User_Id, 
                   u => u.User_Id, 
                   (p, us) => new { Post = p, Users = us })
        .SelectMany(x => x.Users.DefaultIfEmpty(), 
                   (x, u) => new { x.Post, User = u });

    // 关联统计结果并组装PostView
    return postWithUser
        .GroupJoin(commentCounts, 
                   pu => pu.Post.Post_Id, 
                   cc => cc.Post_Id, 
                   (pu, ccs) => new 
                   { 
                       pu.Post, 
                       pu.User, 
                       CommentCount = ccs.DefaultIfEmpty().Sum(cc => cc?.Count ?? 0) 
                   })
        .GroupJoin(imageCounts, 
                   puc => puc.Post.Post_Id, 
                   ic => ic.Post_Id, 
                   (puc, ics) => new PostView
                   {
                       User_Id = puc.Post.User_Id,
                       Post_Id = puc.Post.Post_Id,
                       Author = puc.User?.Username,
                       First_Name = puc.User?.First_Name,
                       Last_Name = puc.User?.Last_Name,
                       Created = puc.Post.Created,
                       Modified = puc.Post.Modified,
                       Content = puc.Post.Content,
                       Profile_Picture = puc.User != null ? puc.User.Profile_Picture : "No picture",
                       Comments = puc.CommentCount,
                       Images = ics.DefaultIfEmpty().Sum(ic => ic?.Count ?? 0)
                   });
}

方案说明 #

• 方案1的子查询写法最简洁，EF Core会将其翻译为SQL中的条件计数，性能最优；
• 方案2通过预统计避免了多层GroupJoin嵌套导致的EF Core翻译失败问题；
• 两种方案均能正确实现左连接用户、统计评论和图片数量的需求。

内容的提问来源于stack exchange，提问作者Martín Cárdenas