How to use Distinct in entity/linq based on certain columns (Remove duplicated values)

I want to remove the duplicated values from the columns ” 1)LabelName, 2)OpenLabelStandard, 3)Type “. The issue is that my table has four columns. beside to the three previous columns I have another one called 4)’Distance’. it is not a field from database. I added it to my table using some calculations in view model. the distance column is changeable for each row so when I’m using Distinct(), it is NOT working because of distance column.

for example, Currently I have like this table below.

LabelName    OpenLabelStandard  Type    Distance

 A1               A2             A3        30
 A1               A2             A3        50
 A1               A2             A3        100
 B1               B2             B3        15
 B1               B2             B3        60

I need the result to be like the following

LabelName    OpenLabelStandard  Type    Distance

 A1               A2             A3        30
 B1               B2             B3        15

This is my select in controller.

    var listdata = (from c in _context.OpenLabelTag  select c )
                .Select(x=> new {x.LabelName,x.OpenLabelStandard,x.Type, Distance = x.Coordinates.Distance(searchArea),x.Coordinates})
  
                   .OrderBy(s=>s.Coordinates.Distance(searchArea))
                   .Where(x=>x.Coordinates.IsWithinDistance(searchArea,InputRadius) && x.OpenLabelStandard !=null)
                   .Take(10).ToList().Distinct() 
                      ;

Do you have any clue how can I solve it ?

Answer

You can’t apply distinct to a subset of properties and still keep the rest of the properties because distinct “groups” values. Other properties that are not included in the distinct result cannot be part of the result because they might have multiple, different values (like your Distance example).

As HoneyBadger suggested, you can simulate a distinct with a GroupBy(), then select all the .Key properties and compute aggregate functions on the rest of the properties:

var listdata = (from c in _context.OpenLabelTag select c)
    .Select(x => new { x.LabelName, x.OpenLabelStandard, x.Type, Distance = x.Coordinates.Distance(searchArea), x.Coordinates })
    .Where(x => x.Coordinates.IsWithinDistance(searchArea, InputRadius) && x.OpenLabelStandard != null)
    .GroupBy(x => new { x.LabelName, x.OpenLabelStandard, x.Type })
    .Select(gr => new
    {
        gr.Key.LabelName,
        gr.Key.OpenLabelStandard,
        gr.Key.Type,
        MinDistance = gr.Min(x => x.Distance)
    })
    .OrderBy(s => s.MinDistance)
    .Take(10).ToList();

Note that I removed the Distinct() and also moved the OrderBy() to after the grouping operation.