How To Use LINQ To SQL For Retrieving Data From SQL Server Database

Create table Departments  
(  
    ID int primary key identity,  
    Name nvarchar(50),  
    Location nvarchar(50)  
)  
GO  
Create table Employees  
(  
    ID int primary key identity,  
    FirstName nvarchar(50),  
    LastName nvarchar(50),  
    Gender nvarchar(50),  
    Salary int,  
    DepartmentId int foreign key references Departments (Id)  
)  

Insert into Departments values ('IT', 'Mumbai');
Insert into Departments values ('HR', 'Pune');
Insert into Departments values ('Payroll', 'Bangalore');
GO
Insert into Employees values ('Akshay', 'Phadke', 'Male', 60000, 1);
Insert into Employees values ('Milind', 'Daware', 'Male', 45000, 3);
Insert into Employees values ('Raghvan', 'Nadar', 'Male', 70060, 1);
Insert into Employees values ('Umesh', 'Thore', 'Male', 45080, 2);
Insert into Employees values ('Kiran', 'Shinde', 'Female', 30000, 2);
Insert into Employees values ('Valarie', 'Vikings', 'Female', 35000, 3);
GO

Notice that it is automatically going to create the entities for those two tables. These entities are mapped to their respective tables. Also, notice that there is an association between the department and the employee table. This association is modeled based on the primary key and foreign key relationship. In the employee table, departmentID is the foreign key.

So, based on that relationship, this LINQ to SQL designer is able to model the association between these two entities.

Please note that the arrow is pointing from department to employees - meaning there is a 1-to-many relationship between department and employees entities. So, a department can have one or more employees.

Now, let’s add a Webform in this project and add a GridView control to that Webform.

Notice that in the web. config file, the connection string will be added and in Sample. designer file, there will be a class called SampleDataContext. We will discuss this class in upcoming articles about LINQ.

For now, just assume that this class is the entry point in the database. So, if we have to get anything from the database, we have to create an instance of this class and then use that to retrieve any data.

So, within the page load event, let's create the instance of that class.

protected void Page_Load(object sender, EventArgs e)
{
    SampleDataContext dbContext = new SampleDataContext();
    GridView1.DataSource = dbContext.Employees;
    GridView1.DataBind();
}

So, we have created the instance of SampleDatacontext and in the GridView Control, we are passing Employees and displaying all the table details. Now, run the form.

And, this is our output. We will filter some data. If we want only males and salary in descending order, our LINQ query will be as follows.

protected void Page_Load(object sender, EventArgs e)
{
    SampleDataContext dbContext = new SampleDataContext();
    GridView1.DataSource = from employee in dbContext.Employees
                            where employee.Gender == "Male"
                            orderby employee.Salary descending
                            select employee;
    GridView1.DataBind();
}

So, let’s actually run this and see if we only get male employees.

So, we got only male employees and they are sorted by salary in descending order. Now, the interesting thing is that we have not returned any T-SQL query but all we have returned is LINQ query. So, how is the application is getting all the data from SQL Server? If you refer to my previous article on LINQ, we have seen LINQ to SQL providers. In there, we learned how the application issues this query to the provider and then it generates a T-SQL query. Then, the SQL Server executes the query and then returns the matching rows.

Conclusion

So, this was all about LINQ to SQL. In the next article, we will see Insert, Update, and Delete using LINQ to SQL.