In this article, I will explain you how to add Linq to SQL class in C# and how to use it with simple example.

What is LINQ to SQL

LINQ to SQL is a component of .NET Framework version 3.5 that provides a run-time infrastructure for managing relational data as objects.

LINQ to SQL is an ORM(Object Relational Mapping) framework that creates .net classes based on the database tables. We can write LINQ queries like select, insert, update and delete using any .net programming language like C#. LINQ to SQL provider converts LINQ expression into SQL query and send to database for its execution.

Advantages of LINQ to SQL

1. Compile time error checking
2. Provide intellisense
3. Provide debugging
The only drawback is, it supports SQL-Server database only.

How to Use LINQ to SQL?

First of all, create a SQL table tblEmployee and insert some records. Below is the script to create table.

create table tblEmployee
(
Id int primary key identity,
Name varchar(50),
Gender varchar(10),
Salary int
)

insert into tblEmployee values('John','Male',50000)
insert into tblEmployee values('Mary','Female',55000)
insert into tblEmployee values('Rahul','Male',40000)
insert into tblEmployee values('Mathew','Male',80000)

Right click on the project menu and select Add New Item.. from visual studio as show below.

Now, the new window will open as show below. Go to Data tab and select LINQ to SQL classes and give its name Demo.dbml and click on the add button.

Now, go to Server Explorer right click on Data Connections and select Add Connection... and new window will open as shown below. Write server name and select your database from dropdown.

Now, drag and drop tblEmployee table from connection to Demo.dbmlsurface.

You can check the all the classes are generated in Demo.designer.cs file. DemoDataContext is the entry point to database as shown below.

In designer file

Now, add a gridview control and add a label control to webform. Below is the designer code.

"gvEmployee" runat="server">


"lblStatus" runat="server">

In code-behind file

Below is the code to bind all Employee to gridview and count the number of Male Employee.

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
    {
        BindGrid();
    }
}

private void BindGrid()
{
    DemoDataContext dbContext = new DemoDataContext();

// bind all employee to gridview
    gvEmployee.DataSource = dbContext.tblEmployees;
    gvEmployee.DataBind();

// select only male employee using LINQ
int Malecount = (from emp in dbContext.tblEmployees
                     where emp.Gender == "Male"
                     select emp).Count();

    lblStatus.Text = "Total number of male Employee: "+Malecount.ToString();
}

Below is the code to bind all male employee to gridview and count the number employee.

private void BindGrid()
{
    DemoDataContext dbContext = new DemoDataContext();

// bind all male employee to gridview using LINQ
    gvEmployee.DataSource = from emp in dbContext.tblEmployees
                            where emp.Gender == "Male"
                            select emp;
    gvEmployee.DataBind();

// count total number of employees
int TotalEmployee = (from emp in dbContext.tblEmployees
                        select emp).Count();

    lblStatus.Text = "Total number of Employee: " + TotalEmployee.ToString();
// or 
    lblStatus.Text = "Total number of Employee: " + dbContext.tblEmployees.Count();          
}