如何在C# LINQ查询中基于ImageID获取去重后的唯一值？

我有数据库数据，已编写SQL连接查询关联PhotoContest.ContestImageLibrary与PhotoContest.ContestCategories两张表，并基于PhotoContestID筛选数据，但希望获取基于ImageID的唯一值，避免重复记录。SQL查询如下：

select *
from PhotoContest.ContestImageLibrary
Left Join 
PhotoContest.ContestCategories On 
PhotoContest.ContestImageLibrary.PhotoContestID = PhotoContest.ContestCategories.PhotoContestID;

我希望基于ImageID获取唯一值（取第一条即可），不想出现重复记录。我编写了以下C# LINQ代码获取数据，但返回了重复结果，尝试在末尾使用Distinct方法也无效：

var voteList = (from i in context.ImageLibrary
                join p in context.ContestCategories on i.PhotoContestID equals p.PhotoContestID
                select new VoteLists()
                {
                    PhotoContestID = i.PhotoContestID,
                    ImageID = i.ImageID,
                    ImageCaption = i.ImageCaption,
                    ImageName = i.ImageName,
                    categoryName = p.categoryName,
                    isImageAccepted = i.isImageAccepted,
                    catID = p.Id,
                    empID = i.empID,
                    votingType = p.votingType
                }).Where(x => x.PhotoContestID == id).ToList();

请问应如何修改后端代码以实现基于ImageID的去重，或者是否无法基于ImageID获取唯一值？

方法1：GroupBy分组取第一条 #

Distinct无效是因为生成的VoteLists是引用类型，默认比较引用地址而非属性值。可以按ImageID分组，再从每组中取第一条记录：

var voteList = (from i in context.ImageLibrary
                join p in context.ContestCategories on i.PhotoContestID equals p.PhotoContestID
                where i.PhotoContestID == id
                select new VoteLists()
                {
                    PhotoContestID = i.PhotoContestID,
                    ImageID = i.ImageID,
                    ImageCaption = i.ImageCaption,
                    ImageName = i.ImageName,
                    categoryName = p.categoryName,
                    isImageAccepted = i.isImageAccepted,
                    catID = p.Id,
                    empID = i.empID,
                    votingType = p.votingType
                })
                .GroupBy(x => x.ImageID)
                .Select(g => g.First())
                .ToList();

方法2：使用DistinctBy（.NET Core 3.0+） #

如果项目基于较新的.NET版本，可直接用DistinctBy指定按ImageID去重：

var voteList = (from i in context.ImageLibrary
                join p in context.ContestCategories on i.PhotoContestID equals p.PhotoContestID
                where i.PhotoContestID == id
                select new VoteLists()
                {
                    PhotoContestID = i.PhotoContestID,
                    ImageID = i.ImageID,
                    ImageCaption = i.ImageCaption,
                    ImageName = i.ImageName,
                    categoryName = p.categoryName,
                    isImageAccepted = i.isImageAccepted,
                    catID = p.Id,
                    empID = i.empID,
                    votingType = p.votingType
                })
                .DistinctBy(x => x.ImageID)
                .ToList();

方法3：先查主表再关联分类 #

若一个PhotoContestID对应多条分类记录，可先获取目标图片记录，再单独查询分类信息（假设取第一个匹配的分类）：

var imageRecords = context.ImageLibrary
    .Where(i => i.PhotoContestID == id)
    .ToList();

var voteList = imageRecords.Select(i => 
{
    var category = context.ContestCategories.FirstOrDefault(p => p.PhotoContestID == i.PhotoContestID);
    return new VoteLists()
    {
        PhotoContestID = i.PhotoContestID,
        ImageID = i.ImageID,
        ImageCaption = i.ImageCaption,
        ImageName = i.ImageName,
        categoryName = category?.categoryName,
        isImageAccepted = i.isImageAccepted,
        catID = category?.Id,
        empID = i.empID,
        votingType = category?.votingType
    };
}).ToList();

原因说明 #

原查询重复是因为一个PhotoContestID对应多个ContestCategories记录，关联后每条图片记录会和所有匹配的分类生成组合，导致同一ImageID出现多条结果。

内容的提问来源于stack exchange，提问作者Rajesh