Jitesh Byahut 3/8/2104 7118

Linq To Sql Tutorial

Introduction:

Language Integrated Query (LINQ) is a language feature in the .NET Framework that enables you to use common syntax to query data in a collection, an XML document, a database, or any type that supports the IEnumerable<T> or IQueryable<T> interface.

Why Need Linq:

Prior to LINQ, a developer needed to learn different syntax depending on the source of the data. If the source were a database, you needed to learn SQL. If the source were an XML document, you needed to learn XQuery. If the source were an array or a collection, you would write a looping structure, such as a foreach loop, that would enumerate through the items in the collection and filter them appropriately. LINQ enables you to use common syntax regardless of what the source is.

Linq provides two different styles of syntax for linq Query, expressions-based are the first style, and method-based queries are the second.

Query expressions search through data stored in an array, a collection, or any type that supports the IEnumerable<T> or IQueryable<T> interfaces. The syntax for a query expression is similar to the syntax when working with SQL.

LINQ query expressions enable you to perform “queries” against the array with syntax similar to SQL except it is C# syntax and the order of the elements is different. The benefit of a query expression is less coding and more readability.

How to add linq in Asp.Net

demoimage

The following sql tables are using for showing Linq's examples: -

Student Table


State Table

Select:

There are two queries in each section which results are same.

The following example showing Linq's select query:-

protected void Page_Load(object sender, EventArgs e)

         {

//linq DataContext object

         DataClassesDataContext db = new DataClassesDataContext();

       

                // Expression based query

         var data = from i in db.tbl_students

                    select i;

        //Method based query

         var data1 = db.tbl_students.Select(i => i);

 

         if (data != null)

         {

             GridView1.DataSource = data;

             GridView1.DataBind();

         }

}

Output:

Filtering:

The following example showing Linq's filter query:-

// Expression based query

         var data = from i in db.tbl_students

                    where i.Branch == "ECE"

                    select i;

         //Method based query

         var data1 = db.tbl_students.Where(i => i.Branch == "ECE");

 

         if (data != null)

         {

             GridView1.DataSource = data1;

             GridView1.DataBind();

         }

Output:


Ordering:

You can sort the results of your query by using the orderby clause in your query. You can order ascending or descending just as you would in a SQL statement.

You can order the elements in a sequence by using the OrderBy(for expressions-based) or the OrderByDescending(for method-based) methods.

// Expression based query

         var data = from i in db.tbl_students

                    orderby i.Name descending

                    select i;

        //Method based query

         var data1 = db.tbl_students.OrderByDescending(i => i.Name);

         if (data != null)

         {

             GridView1.DataSource = data1;

             GridView1.DataBind();

         }

Output:


Projection:

The select clause can return the object in the sequence or return a limited number of properties from the object in the sequence. Selecting a limited number of properties or transforming the result into a different type is referred to as projection.

There is also a SelectMany method that you can use to flatten two sequences into one sequence similar to how a join works.

// Expression based query

         var data = from i in db.tbl_students

                    select new { i.Name, i.Branch, i.City };

         //Method based query

         var data1 = db.tbl_students.Select(i => new { i.Name, i.Branch, i.City });

 

         if (data != null)

         {

             GridView1.DataSource = data;

             GridView1.DataBind();

         }

Output:


Aliasing:

Following example Showing Linq's aliasing query

// Expression based query

         var data = from i in db.tbl_students

                    select new { StudentName = i.Name, BranchName = i.Branch, CityName = i.City };

         //Method based query

         var data1 = db.tbl_students.Select(i => new { StudentName = i.Name, BranchName = i.Branch, CityName = i.City });

         if (data != null)

         {

             GridView1.DataSource = data1;

             GridView1.DataBind();

         }

Output:


Joining:

You can use the join clause to combine two or more sequences of objects similar to how you join tables in a SQL statement.

// Expression based query

         var data = from i in db.tbl_students

                    join s in db.tbl_states on i.StateId equals s.StateId

                    select new { i.Name, i.Branch, i.City, s.StateName };

        //Method based query

         var data1 = db.tbl_students.Join(db.tbl_states,

                     i => i.StateId,

                     s => s.StateId,

                     (i, s) => new { i.Name, i.Branch, i.City, s.StateName });

 

         if (data != null)

         {

             GridView1.DataSource = data;

             GridView1.DataBind();

         }

Output:


Grouping :

You can use the group clause in a query expression to group by a particular property to accomplish this requirement.

The GroupBy method can be used to group by one or more fields. This is equivalent to using the group keyword when creating a query expression.

// Expression based query

         var data = from i in db.tbl_students

                    group i by i.Branch into n

                    select new { Branch=n.Key};

        // Method based query

         var data1 = db.tbl_students.GroupBy(i => i.Branch).Select(i => new { Branch = i.Key });

 

         if (data != null)

         {

             GridView1.DataSource = data;

             GridView1.DataBind();

         }

Output:

demoimage


Please give your feedback for improving this page