When we say LINQ To SQL, it refers to the technology through which we can access Database via LINQ. This has replaced the traditional way of writing SQL Query for Database access, even it has made possible for integrating query in the code.LINQ To SQL is not only used to manipulate database results, it is even used to manipulates array or list collections.

    The first thing to do for using LINQ To SQL is to define a way, by which .NET can recognize the database as Classes and Objects. Therefore we need to map the database tables and stored procedures to 'LinQ to SQL' classes. To do this we first open our project in the solution explorer then right click on the project, then select add new item, then in the data categories select a type named LINQ To SQL Classes, this will give a .dbml file along with the designer interface.     This designer interface has two-parts, one for dragging tables from server explorer and the other for dragging stored procedures etc. After dragging all these items we are done with the mapping process.

Let us take an example for selecting data using LINQ To SQL:

public bool CheckValidUser(string userName, string passWord)
{
DBDataContext myDB = new DBDataContext();
var userResults = from u in myDB.Users
                         where u.Username == userName
                         && u.Password == passWord
                         select u;
return Enumerable.Count(userResults) > 0;
}

  In the above example we have used the syntax very similar to SQL, the above example shows how to validate a user against a given username and password from database. In this example we have first made the object of DBDataContext class (which is a DataContext class and is responsible for .NET To Database communication).Then we are making the object u of class Users and fetching the users having the passed Username and Password. The function Enumerable.Count() counts the number of rows or objects returned by the query.

Retrieving single row using LINQ:

Example:

public User GetUser(string userName)
{
     DBNameDataContext myDB = new DBNameDataContext();
     User user = myDB.Users.Single(u, u.UserName=>userName);
     return user;
}

  The above example explains how to fetch single item or row from database table.In this we first made the object of DataContext class and then we selected the user having the passed Username.

Iterating through all LinQ To SQL returned results:

If we have to iterate through the multiple result returned by the LinQ query, so this can easily be done through the foreach loop as it is better in performance.

Example:

foreach(User user in userResults)
{

    if(user.Role == 'admin')
     {
         //do whatever we want
     }
}

  In the above example the userResults contains the multiple result returned by the LinQ query.