Creating LINQ queries to access the data from class.

In this article we are going to discuss that how we can create the LINQ queries and then with the help of those queries how can we access the data from a class. We will see it with the help of an example of Employee table.

We first create an Employee class and add some properties in it which denotes the
table column and after that we will add some data into it.Below is the sample code for
the class.


public class Employee
{
    //These properties denotes the table columns

    public int EmployeeId { get; set; }
    public string EmployeeName { get; set; }
    public string Department { get; set; }
    public double Salary { get; set; }

    public static List GetEmployees()
    {
        List emp = new List()
        {
            //here we added some data to the table
            new Employee{EmployeeId=100,EmployeeName="Anil",Department="Sales",Salary=30000},
            new Employee{EmployeeId=101,EmployeeName="Rohit",Department="Sales",Salary=20000},
            new Employee{EmployeeId=102,EmployeeName="Neha",Department="Marketing",Salary=25000},
            new Employee{EmployeeId=103,EmployeeName="Priya",Department="Finance",Salary=35000},
            new Employee{EmployeeId=104,EmployeeName="Subhash",Department="Admin",Salary=42000},
           
        };
        return emp;
    }
}

Now we will take a gridview and some buttons on whose clicks gridview will be populated with the specific data
according to the condition applied in queries.

Now in our page we will create diffrent types of queries using LINQ which will access this class
and acess the data.

1. Query to get the employee names in descending order.


protected void btngetdata_Click(object sender, EventArgs e)
    {
        List emp = Employee.GetEmployees();
        var emplist= from e1 in emp 
                     orderby e1.EmployeeName descending
                select new {EmployeeName=e1.EmployeeName};

                GridView1.DataSource=emplist;
                GridView1.DataBind();         
       
    }

2. Query that returns the data of Employee which belongs to Sales department.


protected void btngetdata_Click(object sender, EventArgs e)
    {
        List emp = Employee.GetEmployees();
        var emplist = from e1 in emp
                      where e1.Department.Equals("Sales")
                      select new { EmpName = e1.EmployeeName, Salary = e1.Salary };
              
        GridView1.DataSource = emplist;
        GridView1.DataBind();       
    }

3. Query that returns the average salary of employee.


protected void btngetdata_Click(object sender, EventArgs e)
    {
        List emp = Employee.GetEmployees();
        var emplist = from e1 in emp
                      group e1 by e1.Department into g
                      orderby g.Average(e3 => e3.Salary) descending
                      select new { DeptName = g.Key, TotalEmployee = g.Count(), TotalSalary = g.Sum(e2 => e2.Salary), Average= g.Average(e3 => e3.Salary) };


        GridView1.DataSource = emplist;
        GridView1.DataBind();  
    }

4. Query that returns the salary between specific ranges.


protected void btngetdata_Click(object sender, EventArgs e)
    {
        List emp = Employee.GetEmployees();
        var emplist = from e1 in emp
                      where e1.Salary > 20000 && e1.Salary < 30000
                      select new { EmpName = e1.EmployeeName,Salary=e1.Salary };


        GridView1.DataSource = emplist;
        GridView1.DataBind();         
       
    }

5. Query that returns the data in which employee name begins with S


protected void btngetdata_Click(object sender, EventArgs e)
    {
	List emp = Employee.GetEmployees();
        var emplist = from e1 in emp
                      where e1.EmployeeName.StartsWith("S")
                      select e1;                     

        GridView1.DataSource = emplist;
        GridView1.DataBind(); 
}

Creating LINQ queries to access the data from class.

Comments

About dotnetspider.com

Quick Links

General

Services