Selecting rows with distinct column values using LINQ

Question

I have a generic list of SharePoint list items (List employees)

The SharePoint list contains the two columns, "Employee Name" and "Employee Designation"

I want to get an object List which contains distinct "Employee Designation" column values.

How do I do this using LINQ?

This didnt work for me:

 var designations = from SPListItem employee in employees
                    select new {Designation = employee["Employee Designation"].ToString().Distinct()};

Answer 1

You have to use the Distinct method on the sequence, and you will need to provide a custom implementation of IEqualityComparer<T>, because of this you will need an explicit class for the object you are creating.

var designations = (from SPListItem employee in employees
                   select new 
                   {
                     Designation = employee["Employee Designation"].ToString()
                   }).Distinct(comparer);

Answer 2

If you are only retrieving the string value of that column, you can avoid writing an equality comparer by selecting a string (instead of an object containing a string property).

var designations = (from SPListItem employee in employees
                    select employee["Employee Designation"].ToString())
                   .Distinct()
                   .ToList();