Distinct in Linq based on only one field of the table

Question

I am trying to use .distinct in Linq to get result based on one field of the table (so do not require a whole duplicated records from table).

I know writing basic query using distinct as followed:

var query = (from r in table1
orderby r.Text
select r).distinct();

but I need results where r.text is not duplicated.

Answer 1

Try this:

table1.GroupBy(x => x.Text).Select(x => x.FirstOrDefault());

This will group the table by Text and use the first row from each groups resulting in rows where Text is distinct.

Answer 2

MoreLinq has a DistinctBy method that you can use:

It will allow you to do:

var results = table1.DistictBy(row => row.Text);

The implementation of the method (short of argument validation) is as follows:

private static IEnumerable<TSource> DistinctByImpl<TSource, TKey>(IEnumerable<TSource> source,
    Func<TSource, TKey> keySelector, IEqualityComparer<TKey> comparer)
{
    HashSet<TKey> knownKeys = new HashSet<TKey>(comparer);
    foreach (TSource element in source)
    {
        if (knownKeys.Add(keySelector(element)))
        {
            yield return element;
        }
    }
}

Answer 3

but I need results where r.text is not duplicated

Sounds as if you want this:

table1.GroupBy(x => x.Text)
      .Where(g => g.Count() == 1)
      .Select(g => g.First());

This will select rows where the Text is unique.

Answer 4

Daniel Hilgarth's answer above leads to a System.NotSupported exception With Entity-Framework. With Entity-Framework, it has to be:

table1.GroupBy(x => x.Text).Select(x => x.FirstOrDefault());

Answer 5

Update

As of .NET 6 a new DistinctBy operator has been introduced. However, while this works well for Linq to Objects, it doesn't appear to be working for Linq to Entities. I tested it with EF Core 6.0.6 and SQL server.

Generally speaking, LINQ operators come in pairs to support those two scenarios. For example, Enumerable.Select covers Linq to Objects, Queryable.Select covers Linq to Entities.

Interestingly DistinctBy has both of those flavors as well, but EF Core's SQL Server provider doesn't support it's translation to SQL. The reason, in my opinion, is that it would result in a slow query.

Original Answer

There are lots of discussions around this topic.

You can find one of them here:

One of the most popular suggestions have been the Distinct method taking a lambda expression as a parameter as @Servy has pointed out.

The chief architect of C#, Anders Hejlsberg has suggested the solution here. Also explaining why the framework design team decided not to add an overload of Distinct method which takes a lambda.

Answer 6

From what I have found, your query is mostly correct. Just change "select r" to "select r.Text" is all and that should solve the problem. This is how MSDN documented how it should work.

Ex:

    var query = (from r in table1 orderby r.Text select r.Text).distinct();

Answer 7

data.Select(x=>x.Name).Distinct().Select(x => new SelectListItem { Text = x });

Answer 8

Distinct return you SINGLE column, if that's your case then!!

CountryData.Select(x=>x.COUNTRY_NAME).Distinct()

Else if you need multiple columns distinct by one column. you need to groupby first then select the first item from the group. (in this case the rest items in group with different values will not be returned)

CountryData.GroupBy(a=>a.COUNTRY_NAME).Select(a=>a.First());

Answer 9

try this code :

table1.GroupBy(x => x.Text).Select(x => x.FirstOrDefault());

Answer 10

You can try this:table1.GroupBy(t => t.Text).Select(shape => shape.r)).Distinct();