Generics and LINQ

Generics is a feature that is very useful in ASP.NET. If generic collection is used to store data then quering data from the collection becomes really easy.

Given below is a simple example for creating a generic collection and quering from it using LINQ.

---

1) Create an object collection
a) Define the object "Employee"

public class Employee
{
public Employee(int iID, string strName, string strAddress, int iAge)
{
ID = iID;
Name = strName;
Address = strAddress;
Age = iAge;
}
public int ID { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public int Age { get; set; }
}

b)Add objects to collection

public List GetEmployees()
{

List<Employee> lstEmps = new List<Employee>();

lstEmps.Add(new Employee(1, "Emp 1", "212 Street1", 34));
lstEmps.Add(new Employee(2, "Emp 2", "543 Street2", 31));
lstEmps.Add(new Employee(3, "Emp 3", "3332 Street2", 27));
lstEmps.Add(new Employee(4, "Emp 4", "1011 Street2", 40));
lstEmps.Add(new Employee(5, "Emp 5", "3214 Street4", 33));
lstEmps.Add(new Employee(6, "Emp 6", "5543 Street1", 35));

return lstEmps;
}

2) Query collection using LINQ

List<employee> EmpList =GetEmployees();

/**LINQ Examples**/

//Select employees with age > 30
List<employee> emps1 = (from x in EmpList where x.Age > 30 select x).ToList();

//Select employees with name starting with "Emp"
List<employee> emps2 = (from x in EmpList where x.Name.StartsWith("Emp") select x).ToList();

//Select employee with address "212 Street1"
List<employee> emps3 = (from x in EmpList where x.Address == "212 Street1" select x).ToList();

Delete Duplicate Rows from SQL Server Table

This example is about deleting duplicate rows in SQL server table. In this example duplicate records are deleted from the table based on rank.

/*Create temp table*/

create table #tmp(id int,name1 varchar(20),cre_date datetime)

GO

/*Insert rows with duplicate data in it*/

insert into #tmp values(1,'name1','2011-03-30 9:00 am')
insert into #tmp values(1,'name1','2011-03-30 9:20 am')
insert into #tmp values(2,'name2','2011-03-30 9:30 am')
insert into #tmp values(3,'name3','2011-03-30 9:40 am')
insert into #tmp values(3,'name3','2011-03-30 9:50 am')
insert into #tmp values(3,'name3','2011-03-30 9:55 am')

GO

/*Delete the rows that are duplicate*/

with t as (
Select id,name1,row_number() over (partition by id,name1 order by cre_date) rnk from #tmp )
delete from t where rnk>1