如何优化基于LINQ的SQL多表关联查询过程？

已完成五张数据表的关联操作，但当前通过对comments表分组后取首行获取评论计数的逻辑仍有优化空间，现有实现代码如下：

var groupedComments = _context.UsersPhotoComments.GroupBy(
        p => p.UsersPhotosId, p => p.Comment, (key, g) => new { PhotoId = key, Count = g.Count() });

var photos =
    (
    from p in _context.UsersPhotos
    join albums in _context.UsersAlbums on p.UsersAlbumsId equals albums.Id into GroupAlbums
    from albums in GroupAlbums.DefaultIfEmpty()
    join info in _context.UsersInformation on albums.UsersId equals info.UsersId into GroupUser
    from info in GroupUser.DefaultIfEmpty()
    where (info.Username == req.Username)
    select new
    {
        Photos = p,
    }
    )
    .OrderBy(p => p.Photos.InsertDate)
    .Select(p => new GetUsersInformationPhotosByUsernameServiceDto
    {
        Filename = p.Photos.Filename,
        Id = p.Photos.Id,
        Title = p.Photos.Title,
        Detail = p.Photos.Detail,
        VisitorCounter = p.Photos.VisitorCounter + 1,
        CountComments = groupedComments.Where(gc => gc.PhotoId == p.Photos.Id).Select(gc => gc.Count).First().ToString(),
    })
    .Take(6)
    .ToList();

最终输出效果：

优化方案 #

原方案存在两个核心问题：一是易触发N+1查询或客户端遍历匹配，性能损耗大；二是若照片无评论，First()会直接抛出异常。可将评论计数逻辑整合到主查询中，由数据库一次性完成关联与统计，优化后的代码如下：

var photos =
    (
    from p in _context.UsersPhotos
    join albums in _context.UsersAlbums on p.UsersAlbumsId equals albums.Id into GroupAlbums
    from albums in GroupAlbums.DefaultIfEmpty()
    join info in _context.UsersInformation on albums.UsersId equals info.UsersId into GroupUser
    from info in GroupUser.DefaultIfEmpty()
    // 左连接分组后的评论统计，确保无评论的照片也能获取到0值
    join commentStats in _context.UsersPhotoComments
        .GroupBy(c => c.UsersPhotosId)
        .Select(g => new { PhotoId = g.Key, CommentCount = g.Count() })
    on p.Id equals commentStats.PhotoId into commentGroup
    from commentStat in commentGroup.DefaultIfEmpty()
    where info.Username == req.Username
    select new
    {
        Photos = p,
        CommentCount = commentStat?.CommentCount ?? 0
    }
    )
    .OrderBy(p => p.Photos.InsertDate)
    .Select(p => new GetUsersInformationPhotosByUsernameServiceDto
    {
        Filename = p.Photos.Filename,
        Id = p.Photos.Id,
        Title = p.Photos.Title,
        Detail = p.Photos.Detail,
        VisitorCounter = p.Photos.VisitorCounter + 1,
        CountComments = p.CommentCount.ToString()
    })
    .Take(6)
    .ToList();

优化点说明 #

• 性能提升：将评论分组统计逻辑整合到主查询，由数据库执行关联与计数，避免客户端遍历匹配带来的性能损耗，数据量越大优化效果越明显。
• 鲁棒性增强：通过DefaultIfEmpty()和空合并运算符?? 0处理无评论场景，彻底避免原代码中First()引发的空引用异常。

内容的提问来源于stack exchange，提问作者Ali.M Eghbaldar