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

First, we will learn what LINQ to SQL is.

LINQ to SQL is an ORM (Object Relational Mapping) framework that can automatically create the stored type .NET classes based on the database tables. We can, then, write LINQ to SQL queries like Select, Insert, Update, and Delete, in any .NET supported language. The LINQ to SQL provider then converts those LINQ queries to T-SQL that the SQL Server can understand.

Go through the link for more understanding on LINQ Providers and Introduction of LINQ.

You can download the complete code of this article from here.

LINQ to SQL supports transactions, views, and stored procedures.

NOTE. LINQ supports only the SQL Server Database.

Since LINQ to SQL models a relational database using strongly typed .NET classes, we have the following advantages.

• IntelliSense support
• Compile time error-checking
• Debugging support

Let’s flip to SQL Server Management Studio. First, we will create a table and will insert some data.

Create table "Departments" and "Employees" as shown below.

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)  
)  

Let’s insert some data now.

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

Now, we have data in both tables. Now, create an empty web application. Add a "New item" and select Data -> LINQ to SQL Classes -> Name it Sample.dbml.

Now, drag and drop the tables.

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.