Home > DeveloperSection > Articles > Using LINQ

Using LINQ


LINQ LINQ 
Ratings:
1 Comment(s)
 2968  View(s)
Rate this:

Using LINQ

In this article we will discuss the LINQ approach introduced in .NET 3.5 for querying.

Introduction

§  LINQ is a new technology introduced in .NET 3.5

§  LINQ is acronym for Language Integrated Query

§  The LINQ feature is one of the major difference between .NET 3.0 and .NET 3.5 framework

§  LINQ is designed to fill the gap that exists between tradition .NET, which offers strong typing and full Object-Oriented approach

LINQ introduces an easy learn approach for querying and modifying data and can support querying various types of data sources including relational data, XML and in-memory data structures.

Advantages of Using LINQ

     Using LINQ offers with the following advantages:

1.      LINQ offers common syntax for querying any type of data sources

2.      Secondly, it binds the gap between relational and object-oriented approach

3.      LINQ expedites development time by catching errors at compile time and includes IntelliSense & Debugging support

4.      LINQ expressions are Strongly Typed.

What we mean by Strongly Typed?

     Strongly typed expressions ensure access to values as correct type at compile time & prevent type mismatch errors being caught when the code is compiled rather at run-time.

Core Assemblies in LINQ

   The core assemblies in LINQ are

1.      using System.Linq

     Provides Classes & Interface to support LINQ Queries

2.      using System.Collections.Generic

     Allows User to create Strongly typed collections that provide type safety and performance (LINQ to Objects)

3.      using System.Data.Linq

     Provides functionality for access to relational databases(LINQ to SQL)

4.      using System.Xml.Linq

     Provides functionality for accessing XML documents using LINQ(LINQ to XML)

5.      using System.Data.Linq.Mapping

     Designates a class as an entity associated with a database.

Types of LINQ

     LINQ provides three basic types of queries, each type offers specific functionality and is designed to query specific source

1.      LINQ to Objects

2.      LINQ to XML(or XLINQ)

3.      LINQ to SQL(or DLINQ)

Let us discuss each of them in brief,

LINQ to Objects

§  LINQ to Object is the basic of LINQ

§  It enables us to perform complex query operations against any enumerable object(object that supports IEnumerable interface)

LINQ queries provides following advantages over traditional foreach loops

1.      They are more concise & readable specially when filtering multiple conditions

2.      Provides powerful filtering, grouping and ordering with little coding.

Coding a Simple LINQ to Objects Query

     Here, we will use LINQ to Objects to retrieve numbers greater than zero from an array of numbers

Steps:

1.      Create a new Project and give a name UsingLinq.

Using LINQ

2.      Add a ListView control to the Form1. In the Form’s Load event  add the following code

           

int[] arr = { 20, -12, 97, 86, -12, 77, 12, 0, 17, -87, 78 };

 

 

            IEnumerable<int> FilteredValues = from val in arr

                                              where val > 0

                                              select val;

 

            listView1.View = View.List;

            foreach (var values in FilteredValues)

             listView1.Items.Add(values.ToString());

 

When running the code, we get the output as

Using LINQ

This displays the list of numbers greater than Zero.

Explanation

     In this we declare an integer array called arr.

     int[] arr = { 20, -12, 97, 86, -12, 77, 12, 0, 17, -87, 78 };

 

To retrive the numbers greater than Zero from the int array, we query the int arry using IEnumerable<int> and loop through the int array arr with  foreach using the LINQ to Objects query syntax.

LINQ to XML

LINQ to XML provides in-memory XML programming API that integrates XML querying capabilities.

LINQ to XML provides facilities to edit XML documents and elements trees in memory as well as streaming facilities.

Coding LINQ to XML Query

     We will use LINQ to retrieve element values from an XML document. Here we will retrieve the element ENAME using XLINQ

Employee.xml

 

<?xml version="1.0" ?>

 <EMPLOYEES>

 <EMP>

  <EMPNO>7369</EMPNO>

  <ENAME>SMITH</ENAME>

  <JOB>CLERK</JOB>

  <MGR>7902</MGR>

  <HIREDATE>17-DEC-80</HIREDATE>

  <SAL>800</SAL>

  </EMP>

 <EMP>

  <EMPNO>7499</EMPNO>

  <ENAME>ALLEN</ENAME>

  <JOB>SALESMAN</JOB>

  <MGR>7698</MGR>

  <HIREDATE>20-FEB-81</HIREDATE>

  <SAL>1600</SAL>

  <COMM>300</COMM>

  </EMP>

 <EMP>

  <EMPNO>7521</EMPNO>

  <ENAME>WARD</ENAME>

  <JOB>SALESMAN</JOB>

  <MGR>7698</MGR>

  <HIREDATE>22-FEB-81</HIREDATE>

  <SAL>1250</SAL>

  <COMM>500</COMM>

  </EMP>

 <EMP>

  <EMPNO>7566</EMPNO>

  <ENAME>JONES</ENAME>

  <JOB>MANAGER</JOB>

  <MGR>7839</MGR>

  <HIREDATE>02-APR-81</HIREDATE>

  <SAL>2975</SAL>

  </EMP>

 <EMP>

  <EMPNO>7654</EMPNO>

  <ENAME>MARTIN</ENAME>

  <JOB>SALESMAN</JOB>

  <MGR>7698</MGR>

  <HIREDATE>28-SEP-81</HIREDATE>

  <SAL>1250</SAL>

  <COMM>1400</COMM>

  </EMP>

  </EMPLOYEES>

 

 

1.      Add a New Windows Form to the Project and name it FrmLoadXML.

2.      Add ListView control and a button to the Form.

3.      Change the buttons Name to btnLoadXML using Name property

Paste the code below in button Click Event

//Loading the Employee.xml in memory

            XElement xdoc = XElement.Load(@"D:\Employee.xml");

 

            //Query XML doc

            var vExmployees = from EmployeeNames in xdoc.Descendants("ENAME")

                              select EmployeeNames.Value;

 

            // Displaying details

 

            listView1.View = View.List;

            foreach (var EmpNames in vExmployees)

                listView1.Items.Add(EmpNames);

 

When running the Form we get the output with the list of Employee names (ENAME column) from the Employee.xml file

Using LINQ

LINQ to SQL

LINQ to SQL is the last form of LINQ, which provides functionalities to query SQL-based data sources

Namespace

     Its functionalities are located in System.Data.Linq assembly.

using System.Data.Linq;

A Simple LINQ query requires 3 things

1.  Entity Class

2.  Data Context

3.  LINQ query

Coding LINQ to SQL Query

     In this we will use LINQ to SQL to retrieve all employee details from EMP Table

1.      Navigate to Solution Explorer and add a new form named FrmSQLLinq.cs

2.      Add a DataGridView control ,button controls as shown

 

Using LINQ

 

In the Button click event of Load...  We have used LINQ to SQL to retrieve Employee details from the EMP Table

 

Program:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.Linq;

using System.Xml.Linq;

using System.Data.Linq.Mapping;

 

namespace UsingLinq

{

    public partial class FrmSQLLinq : Form

    {

        [Table]

        public class EMP

        {

            [Column]

            public int EMPNO;

            [Column]

            public string ENAME;

            [Column]

            public string JOB;

            [Column]

            public int SAL;

        }

 

 

        public FrmSQLLinq()

        {

            InitializeComponent();

        }

 

        private void btClose_Click(object sender, EventArgs e)

        {

            this.Close();

        }

 

        private void btnLoad_Click(object sender, EventArgs e)

        {

            // Connection String to the Server

            string connString = @"Data Source=yourServer;Initial Catalog=DatabaseName;user id=UserName;password=yourPwd;";

 

            // create data context

            DataContext db = new DataContext(connString);

 

            // create typed table

            Table<EMP> employees = db.GetTable<EMP>();

 

            // Query Database

            var emp = from c in employees select c;

           

            dgEmployee.ColumnCount = 4;

            dgEmployee.Columns[0].Name ="Emp No.";

            dgEmployee.Columns[1].Name ="Emp Name";

            dgEmployee.Columns[2].Name ="Job";

            dgEmployee.Columns[3].Name ="Salary";

 

            foreach (var c in emp)

                dgEmployee.Rows.Add(c.EMPNO, c.ENAME, c.JOB, c.SAL);

        }

    }

}

 

When running the application we get the output as

Using LINQ

Explanation

As you know now that to work with SQL LINQ requires,

1.  Entity Class

2.  Data Context

3.  LINQ query

First, we define an entity class,

[Table]

        public class EMP

        {

            [Column]

            public int EMPNO;

            [Column]

            public string ENAME;

            [Column]

            public string JOB;

            [Column]

            public int SAL;

        }

 

Entity classes provide objects in which LINQ stores data from data sources. They are simply like other C# class.

[Table] – The Table attribute marks the class as an entity class and has an optional Name property that can be used to give the name of a Table, which default to the class name. That why we name the class EMP rather than Employee.

 

Here EMP is the Table in the underlying database

 

[Column] -  The column Attribute sets the field as one that will hold data from Table. You can declare fields in an entity class that don’t map to Table columns, but must be of type compatible with the table columns they map to.

 

// create data context

            DataContext db = new DataContext(connString);

 

A data Context manages the Connection to the Data Source, It also translates LINQ requests into SQL, passes the SQL to the database server and create objects from the result set.

 

// create typed table

            Table<EMP> employees = db.GetTable<EMP>();

A typed table is a collection usually of type System.Data.Linq.Table<T> whose elements are of  Specific type.

 

GetTable Method

     The GetTable method of the DataContext tells the data context to access the results and indicates where to place them.

Here we get all the rows from the Employee Table, the data context creates an object for each row in the customers typed table.

// Query Database

            var emp =

Here we declare implicitly typed local variable emp of type var.

    from c in employees

                      select c

                      ;

select c, is like a SQL Select *, gets all columns from the EMP class.

Article Sum-Up

In this article we have tried to learn about the Technology called LINQ introduced in .NET 3.5 and the functionalities of each of the types of LINQ and how to use each of these types.


Nice Article!

By AVADHESH PATEL on   3 years ago
Thanks for sharing your knowledge. This is very helpful for me.
Keep it up.

you can prefer below article on this topic


 

Don't want to miss updates? Please click the below button!

Follow MindStick