How to remove or get distinct values in C# LINQ query?-CodePudding

Please find the below database data I have written a join query to join two tables and then select the data based on PhotoContestID, but I want unique values Below is the join query and its result

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

As you can see from the data, I want to get unique values based on ImageID FirstorDefault. I don't want duplicated records, like for eg, ImageID

I have written the below code to get the details but I am getting duplicates. I have tried using Distinct at the end but no use.

 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();

            foreach (var image in voteList)
            {
                var f = Url.Content(System.IO.Path.Combine("~/Photos", image.ImageName));
                image.imageUrl = f;

            }

What shall I write exactly in my backend to get the desired unique result or maybe I can't get unique values based on ImageID.

Thanks

CodePudding user response：

Add SELECT DISTINCT <rest of query> in SQL or add a .Distinct() before .ToList() in your c#

CodePudding user response：

The problem is that multiple categories match each single image.

I would say you need to use a GroupBy with all duplicated fields in the key, and that will force you to specify how to manage the multiple matching categories in the value part.