[c#] Distinct in Linq based on only one field of the table

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.

This question is related to c# sql linq

The answer is


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.


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

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

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.


try this code :

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

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;
        }
    }
}

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


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

Examples related to c#

How can I convert this one line of ActionScript to C#? Microsoft Advertising SDK doesn't deliverer ads How to use a global array in C#? How to correctly write async method? C# - insert values from file into two arrays Uploading into folder in FTP? Are these methods thread safe? dotnet ef not found in .NET Core 3 HTTP Error 500.30 - ANCM In-Process Start Failure Best way to "push" into C# array

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to linq

Async await in linq select How to resolve Value cannot be null. Parameter name: source in linq? What does Include() do in LINQ? Selecting multiple columns with linq query and lambda expression System.Collections.Generic.List does not contain a definition for 'Select' lambda expression join multiple tables with select and where clause LINQ select one field from list of DTO objects to array The model backing the 'ApplicationDbContext' context has changed since the database was created Check if two lists are equal Why is this error, 'Sequence contains no elements', happening?