Thursday, May 10, 2012

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
LINQ to SQL - Sub Queries
LINQ to SQL – Join 2 Tables



Search Flipkart Products:
Flipkart.com

No comments: