LINQ to SQL – Select DISTINCT Values

LINQ to SQL – Select DISTINCT Values

Before writing a LINQ to SQL query, we first need to create a DataContext using the LINQ to SQL Classes template, to know more on how to create the DataContext refer to the post LINQ to SQL Sample

Once the DataContext is created we can query the Object model using LINQ queries, let us consider the Employee table which has the following structure.

In practical situations we will have to remove duplicate rows and get only unique rows for reporting. Assume we have the following data in the Employee Table

Table: Employee

ID	NAME	DOB	DOJ	Phone	Email	DepartmentID	Salery
1	Tom	11/11/1967	05/01/2009	123-123-1234	tom@abcsoftware.com	1	5000
2	Harry	10/07/1973	05/01/2009	123-123-1234	harry@abcsoftware.com	2	6000
3	Peter	12/07/1975	05/01/2009	111-222-3333	peter@abcsoftware.com	3	6500
4	John	05/04/1981	05/01/2009	111-222-3333	john@abcsoftware.com	3	7500
5	Charles	12/12/1973	05/01/2009	666-444-2222	charles@abcsoftware.com	4	6500

A normal select query will fetch the following results

Phone	DepartmentID
123-123-1234	1
123-123-1234	2
111-222-3333	3
111-222-3333	3
666-444-2222	4

In SQL Query to can use the DISTINCT clause to filter out duplicate rows as follows

SELECT DISTINCT Phone, DepartmentID

FROM         Employee

This query will fetch the following data

Phone	DepartmentID
111-222-3333	3
123-123-1234	1
123-123-1234	2
666-444-2222	4

Now let us see how the same can be achieved using a LINQ query.

EmployeeClassesDataContext dbContext = new EmployeeClassesDataContext();

var emp = (from e in dbContext.Employees

          select new { e.Phone, e.DepartmentID}).Distinct();

Related Posts

What is LINQ?

LINQ to SQL

LINQ to SQL Sample

LINQ to SQL Select Query with Specific columns

LINQ to SQL Select query with Filter (WHERE Clause)
LINQ to SQL Select Query with Alias Name

LINQ to SQL - Sub Queries
LINQ to SQL – Join 2 Tables