为LINQ查询添加Distinct/GroupBy去重的问题及解决方法

问题场景 #

使用包含多表左连接的LINQ查询获取新闻列表时，结果出现重复数据。原查询代码如下：

var listNews = await (from l in _context.Newss
                    join si in _context.AssignSites on l.NewsId equals si.ParentId into sij
                    from subsi in sij.DefaultIfEmpty()
                    join se in _context.AssignSections on l.NewsId equals se.ParentId into sej
                    from subse in sej.DefaultIfEmpty()
                    join me in _context.AssignMembers on l.NewsId equals me.ParentId into mej
                    from subme in mej.DefaultIfEmpty()
                    where subsi.Site == user.BelongSite || subse.SectionName == user.Section || subme.FaxCode == user.UserName || l.CreatedBy == user.FullName
                    orderby l.CreatedWhen descending
                    select new NewsIndexVm{
                        Id = l.NewsId,
                        Title = l.NewsTitle,
                        Thumbnail = l.Thumbnail,
                        CreatedBy = l.CreatedBy,
                        CreatedWhen = l.CreatedWhen,
                        BelongSite = l.BelongSite
                    })
                    .ToListAsync();

尝试去重时遇到的异常 #

• 在ToListAsync()前添加.Distinct()，生成的SQL语句中orderby子句消失，导致结果无法按创建时间排序
• 若在查询物化（调用ToListAsync()之后）再执行Distinct，结果中仍存在重复记录

有效解决方案 #

将Distinct和排序操作移至LINQ查询外部，先执行去重再进行排序，修正后的代码如下：

var listNews = await (from l in _context.Newss
                join si in _context.AssignSites on l.NewsId equals si.ParentId into sij
                from subsi in sij.DefaultIfEmpty()
                join se in _context.AssignSections on l.NewsId equals se.ParentId into sej
                from subse in sej.DefaultIfEmpty()
                join me in _context.AssignMembers on l.NewsId equals me.ParentId into mej
                from subme in mej.DefaultIfEmpty()
                where subsi.Site == user.BelongSite || subse.SectionName == user.Section || subme.FaxCode == user.UserName || l.CreatedBy == user.FullName
                select new NewsIndexVm{
                    Id = l.NewsId,
                    Title = l.NewsTitle,
                    Thumbnail = l.Thumbnail,
                    CreatedBy = l.CreatedBy,
                    CreatedWhen = l.CreatedWhen,
                    BelongSite = l.BelongSite
                })
                .Distinct()
                .OrderByDescending(x=>x.CreatedWhen)
                .ToListAsync();

内容的提问来源于stack exchange，提问作者Phan Hien Vo