Language Integrated Query (LINQ)

Dheeraj Kumar Gunti

Reading Time : ( words)

LINQ (Language Integrated Query) is a uniform programming model for any kind of data. LINQ enables you to query and manipulate data with a consistent model that is independent from data sources. LINQ is the methodology that simplifies and unifies the implementation of any kind of data access. LINQ does not force you to use a specific architecture; it facilitates the implementation of several existing architectures for accessing data.

Following is the example for how to create an application by using LINQ.

Create an empty ASP.NET application and create a database named Person through SQL Express or else connect with SQL server studio edition. Create a table named tblPerson with the following fields.




Once the schema is done. Right click on the solution and select add new item and choose LINQ to SQL Classes template as below.



Once the .dbml file is added to your application then Object Relational Designer will open so that we can attach the database schema to the LINQ designer. Drag and drop the table from the database explorer to Object Relational Designer.



We will now move on to how to interact the database with the help LINQ programming. Following is the LINQ query which returns all the person details

PersonLinqDataContext dbContext = new PersonLinqDataContext();
var PersonData = from c in dbContext.TblPersons
                    select c;

Following is the LINQ query which inserts data in to database.

// Creating the object for PersonLinqDataContext
            PersonLinqDataContext dbContext = new PersonLinqDataContext();
// Creating the object for TblPerson Class         
            TblPerson person = new TblPerson();
// Assigning values to the propeties under the TblPerson Class
            person.NAME = "Testing Name"; // Name Field
            person.AGE = 28; // Age Field
            person.ADDRESS = "Testing Addres"; // Address Field
            person.PHONENUM = "23456778"; // Phone Num field
// Adds an entity in a pending insert state to this Table
            dbContext.TblPersons.InsertOnSubmit(person);
// Computes the set of modified objects to be inserted, updated, 
// or deleted, and executes the appropriate commands
// to implement the changes to the database.
            dbContext.SubmitChanges();

Following is the LINQ Query which updates data in to database.

// Creating the object for PersonLinqDataContext
        PersonLinqDataContext dbContext = new PersonLinqDataContext();
// fetching the data from tblpersons based on ID
var PersonData = from c in dbContext.TblPersons
                         where c.ID == 1
                         select c;
// Assigning the changed values 
foreach (var item in PersonData)
{
            item.NAME = "Testing Name 123";
            item.ADDRESS = "Testing Address India";
            item.PHONENUM = "5495792348";
}
// As we assigned modified values to the Tblperson 
// we are submitting the changes.
        dbContext.SubmitChanges();

Based on the update process wrote above the details in the database are modified as below.




Following is the LINQ query which deletes the data from the database based on ID

// Creating the object for PersonLinqDataContext
        PersonLinqDataContext dbContext = new PersonLinqDataContext();
// fetching the data from tblpersons based on ID
var PersonData = (from c in dbContext.TblPersons
                          where c.ID == 2
                          select c).FirstOrDefault();
// FirstorDefault - Returns the first element of a sequence, 
// or a default value if the sequence contains no elements.
        
// we are calling Delete on Submit method which deletes the 
// value from tblPerson table.
        dbContext.TblPersons.DeleteOnSubmit(PersonData);
// we are submitting the changes.
        dbContext.SubmitChanges();

The above is the simple example of how we use LINQ for CRUD operations.