How to apply distinct to linq query in c#?

Question

I have this query

from d in db.v_Report_CompanySearches
where d.PersonName.ToLower().Contains(mPersonName)
orderby d.PersonName ascending
group d by d.PersonName into g
select (g);

But it returns all columns, when I just want PersonName. Plus I want to make sure PersonName is distinct, right now it returns duplicate values.

What is wrong here?

Answer 1

Call Dictinct at the end of query using parenthesis, you don't need the grouping thing there.

var result  = (from d in db.v_Report_CompanySearches
               where d.PersonName.ToLower().Contains(mPersonName)
               orderby d.PersonName ascending
               select d.PersonName ).Distinct();

Answer 2

original question: Is there way to use Distinct in LINQ query syntax?

accepted answer:

The Distinct extension method in LINQ does not have a query syntax equivalent.

See http://blogs.msdn.com/b/charlie/archive/2006/11/19/linq-farm-group-and-distinct.aspx for additional information as to why.

answer #2

There is no Distinct() method syntax in the language integrated query syntax. The closest you could do would be to move the current call:

var q = (from c in tbl
         select c.TABLE_TYPE).Distinct();

Answer 3

Use g.First()

from d in db.v_Report_CompanySearches
where d.PersonName.ToLower().Contains(mPersonName)
orderby d.PersonName ascending
group d by d.PersonName into g
select (g.First().PersonName);

Answer 4

Use select (g.key) to select the grouping key only.

from d in db.v_Report_CompanySearches
where d.PersonName.ToLower().Contains(mPersonName)
orderby d.PersonName ascending
group d by d.PersonName into g
select (g.Key);

in your case the result will be an Enumerable<string> assuming PersonName is a string.

If you use the g.First() method as proposed by another answer, the result will be an IEnumerable<CompanySearch> assuming db.v_Report_CompanySearches is a list of CompanySearch. The result will contain only distinctive values for the PersonName but the other fields are just filled with the first match.

If you just select(g) the result is an Enumerable<IGrouping<string, CompanySearch>> The result will contain the Personnames distinctive as a key (since you group by them) but contains all the results as values. (You can extract them by using a SelectMany statement, see How to get values from IGrouping).

Since my answer got downvoted I make another example, everyone can execute and check the validiy of it:

class Person
{
    public string Name { get; set; }
    public int Age { get; set; }
    public override string ToString()
    {
        return $"{Name}: {Age}";
    }
}

static void Main(string[] args) {
    personList = new List<Person>()
    {
        new Person {Name = "Jay", Age = 25},
        new Person {Name = "Hans", Age = 25},
        new Person {Name = "Fritz", Age = 25},
        new Person {Name = "Hello", Age = 26},
        new Person {Name = "World", Age = 27},
        new Person {Name = "Whatever", Age = 26},
        new Person {Name = "ASDF", Age = 24},
        new Person {Name = "Foo", Age = 25},
        new Person {Name = "Bar", Age = 22},
    };
    var ages = from p in personList
               where p.Age > 22
               orderby p.Age ascending
               group p by p.Age into g
               select (g);

    Console.WriteLine($"select(g): {ages.GetType()}");

    var ages2 = from p in personList
        where p.Age > 22
        orderby p.Age ascending
        group p by p.Age into g
        select (g.First());

    Console.WriteLine($"select(g.First()): {ages2.GetType()}");

    var ages3 = ages.First();
    Console.WriteLine($"(select(g)).First(): {ages3.GetType()}");
}

In the output you can see that the first one is an IEnumerable<IGrouping<int,Person>>, the second output is an IEnumerable<Person> and the third one is an IGrouping<int,Person>.