LINQ (Language-Integrated Query)

Introduction

In this article, I’ll describe how to write LINQ query. We can use LINQ query with any .net platform applications, But in this article we will perform basic linq operations using LINQ To Objects and LINQ To Entity in Asp.Net application.

LINQ (Language-Integrated Query)

Language-Integrated Query (LINQ) is a set of features that provides powerful query capabilities to the language syntax of C# and Visual Basic. LINQ introduces standard, easily-learned patterns for querying and updating data, and the technology can be extended to support potentially any kind of data store.

LINQ Implementation

LINQ is a technology that covers many data sources. We used following type of LINQ implementations that Microsoft has provided.

LINQ to Objects

LINQ to ADO.NET

• LINQ to SQL
• LINQ to Entities
• LINQ to DataSet

LINQ to XML

PLINQ (Parallel LINQ)

LINQ to Objects

LINQ to Objects represents a new approach to collections. It provides the facility to query with any IEnumerable or IEnumerable<T> collection directly without the use of an intermediate LINQ provider. We can use LINQ to query any enumerable collections like List, Array, or Dictionary. The collection may be user-defined or may be returned by a .NET Framework API.

LINQ to ADO.NET

LINQ to ADO.NET includes different LINQ implementations that share the need to manipulate relational data. It also includes other technologies that are specific to each particular persistence layer:

LINQ to SQL

It handles the mapping between custom types in the .NET Framework and the physical table schema in SQL Server.

LINQ to Entities

 LINQ to Entities provides Language-Integrated Query (LINQ) support that enables developers to write queries against the Entity Framework conceptual model. LINQ to Entities converts Language-Integrated Queries (LINQ) queries to command tree queries, executes the queries against the Entity Framework, and returns objects that can be used by both the Entity Framework and LINQ.

LINQ to DataSet

It is an easy and faster way to query data cached in a DataSet object. It also allow LINQ to query over any database that can be query with Ado.Net.

LINQ to SQL and LINQ to Entities have similarities because they both access information stored in a relational database and operate on object entities that represent external data in memory. The main difference is that they operate at a different level of abstraction. Whereas LINQ to SQL is tied to the physical database structure, LINQ to Entities operates over a conceptual model (business entities) that might be far from the physical structure (database tables).

LINQ to XML

It provides an improved XML programming interface. Using this we can query, modify xml document and also save document after modification. System.Xml.Linq namespace contains classes for LINQ to XML.

PLINQ (Parallel LINQ)

PLINQ was introduced in .Net framework 4.0. It extends LINQ to Objects with a new parallel programming library. Using this, we can break/split up a query to execute simultaneously/parallel on different processors.

LINQ Queries

A query is an expression that retrieves data from a data source. Queries are usually expressed in a specialized query language. LINQ simplifies this situation by offering a consistent model for working with data across various kinds of data sources and formats. In a LINQ query, we are always working with objects.

Query operation consists of three distinct actions:

• Obtain the data source.
• Create the query.
• Execute the query.

The following example shows how the three parts of a query operation are expressed in source code.

// The Three Parts of a LINQ Query: 
        //  1. Data source. 
        int[] numbers = new int[7] { 0, 1, 2, 3, 4, 5, 6 };

        // 2. Query creation. 
        // numQuery is an IEnumerable<int> 
        var numQuery =
            from num in numbers
            where (num % 2) == 0
            select num;

        // 3. Query execution. 
        foreach (int num in numQuery)
        {
            Console.Write("{0,1} ", num);
        }


</int>

Data Source

In the above code data source is an array; it implicitly supports the generic IEnumerable interface. A bove query is executed with foreach statement, and foreach requires IEnumerable or IEnumerable.

In LINQ to SQL, we first create an object-relational mapping at design time either manually or by using the Object Relational Designer (O/R Designer).

In the example, tt_WCF_Lab1_Products represents a specific table in the database, and the type of the query result, IQueryable, derives from Enumerable.

LabDbEntities sqlObj = new LabDbEntities();

IQueryable<tt_wcf_lab1_products> productQuery = from prds in   sqlObj.tt_WCF_Lab1_Products
                            select new
                            {
                                prds.ProdSysId,
                                prds.Name,
                                prds.Price

                            };



</tt_wcf_lab1_products>

The Query

The query specifies what information to retrieve from the data source or sources. Optionally, a query also specifies how that information should be sorted, grouped, and shaped before it is returned. A query is stored in a query variable and initialized with a query expression.

The query expression contains three clauses: from, where and select. The from clause specifies the data source, the where clause applies the filter, and the select clause specifies the type of the returned elements.

Query Execution

Deferred Execution

The query variable itself only stores the query commands. The actual execution of the query is deferred until you iterate over the query variable in a foreach statement. Below see the example.

//  Query execution.  
foreach (int num in numQuery)
{
    Console.Write("{0,1} ", num);
}

The foreach statement is also where the query results are retrieved.

Forcing Immediate Execution

Queries that perform aggregation functions over a range of source elements must first iterate over those elements. Examples of such queries are Count, Max, Average, and First. These execute without an explicit foreach statement because the query itself must use foreach in order to return a result.

var evenNumQuery = 
    from num in numbers
    where (num % 2) == 0
    select num;

int evenNumCount = evenNumQuery.Count();

To force immediate execution of any query and cache its results, you can call the ToList<tsource> or ToArray<tsource> methods.

List<int> numQuery2 =
    (from num in numbers
     where (num % 2) == 0
     select num).ToList();

// or like this: 
// numQuery3 is still an int[] 

var numQuery3 =
    (from num in numbers
     where (num % 2) == 0
     select num).ToArray();


</int>

Basic LINQ Query Operations using LINQ to Objects

LINQ to Strings

LINQ can be used to query and transform strings and collections of strings. We will write LINQ queries on string to find distinct words in the string and duplicate words in the string.

string input = "this is linq query"; 
var wordsorderbylength = from str in input.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries) 
                            orderby str.Length 
                            select new { WordsOrderByLength = str };

LINQ to String Array

We will write LINQ queries will find the months starting with "J" in the string array.

string[] ary = new string[] { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" }; 
var res = from months in ary 
            where months.StartsWith("J") 
            select new { MonthStartWith_J = months }; 

LINQ to Int Array

We will write LINQ query will find minimum 3 numbers from the int array.

int[] ary5 = new int[] { 10, 27, 35, 40, 50, 11, 23, 25, 39, 22, 36 }; 
var minvalues = (from values in ary5 
                    orderby (int)values ascending 
                    select new { Min3Values = values }).Take(3); 

LINQ to Files and Directories

We will write LINQ query will find minimum 3 numbers from the int array.

System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(@"E:\images"); 
var giffiles = from file in dir.GetFiles() 
                where file.Extension == ".gif" 
                orderby file.Length 
                select new { FileName = file.Name, FileSize = (file.Length / 1024) + " KB" };

Basic LINQ Query Operations using LINQ to Entities

LINQ-to-Entities operate on entity framework entities to access the data from the underlying database.

Obtaining a Data Source

In a LINQ query, the first step is to specify the data source. In a LINQ query, the from clause comes first in order to introduce the data source (Lab1_Products) and the range variable (prod).

//queryAllCustomers is an IEnumerable< Lab1_Products >
LabDbEntities sqlObj = new LabDbEntities();
var queryAllProducts = from prod  in sqlObj.Lab1_Products select prod;

Filtering

Probably the most common query operation is to apply a filter in the form of a Boolean expression. The filter causes the query to return only those elements for which the expression is true. The result is produced by using the where clause. The filter in effect specifies which elements to exclude from the source sequence

LabDbEntities sqlObj = new LabDbEntities();

            var queryAllProducts = from prod in sqlObj.Lab1_Products
                           where prod.Price > 2000
                           select prod

Ordering

The orderby clause will cause the elements in the returned sequence to be sorted according to the default comparer for the type being sorted.

LabDbEntities sqlObj = new LabDbEntities();

            var queryAllProducts = from prod in sqlObj.Lab1_Products
                           where prod.Price > 2000
                           orderby prod.Name
                           select prod

Joining

Join operations create associations between sequences that are not explicitly modeled in the data sources.

The following are three most common join types:

• Inner join
• Group join
• Left outer join

Inner join

Inner join returns only those records or rows that match or exists in both the tables.

LabDbEntities sqlObj = new LabDbEntities();

            var products = from prds in sqlObj.Lab1_Products
                           join inv in sqlObj.Lab1_Inventory on prds.ProdSysId equals inv.ProdSysId
                           select new
                           {
                               prds.ProdSysId,
                               prds.Name,
                               prds.Price,
                               inv.Inven

                           };

Group join

When a join clause use an INTO expression, then it is called a group join. A group join produces a sequence of object arrays based on properties equivalence of left collection and right collection. If right collection has no matching elements with left collection then an empty array will be produced.

LabDbEntities sqlObj = new LabDbEntities();

            var products = from prds in sqlObj.Lab1_Products
                           join sls in sqlObj.Lab1_SalesTrx on prds.ProdSysId equals sls.ProdSysId into g
                           select new
                           {
                               prds.ProdSysId,
                               prds.Name,
                               prds.Price,
                               TotalSales = g.Sum(x => (decimal?)x.Qnt)

                           };

Left join or Left outer join

LEFT JOIN returns all records or rows from left table and from right table returns only matched records. If there are no columns matching in the right table, it returns NULL values.

In LINQ to achieve LEFT JOIN behavior, it is mandatory to use "INTO" keyword and "DefaultIfEmpty()" method. We can apply LEFT JOIN in LINQ.

LabDbEntities sqlObj = new LabDbEntities();

            var products = from prds in sqlObj.Lab1_Products
                           join cur in sqlObj.Lab1_Currency on prds.CurrencyId equals cur.CurrencyId
                           into prd
                           from prdts in prd.DefaultIfEmpty() 
                           select new
                           {
                               prds.ProdSysId,
                               prds.Name,
                               prds.Price,
                               prdts.CurrencyName

                           };

Cross join

Cross join is a cartesian join means cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records or rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table.

In LINQ to achieve CROSS JOIN behavior, there is no need to use Join clause and where clause.

LabDbEntities sqlObj = new LabDbEntities();

            var products = from prds in sqlObj.Lab1_Products
                           from cur in sqlObj.Lab1_Currency 
                           select new
                           {
                               prds.ProdSysId,
                               prds.Name,
                               prds.Price,
                               cur.CurrencyName

                           };

Conclusion

In this tutorial, we learnt how to perform basic linq operations using LINQ To Objects and LINQ To Entity in Asp.Net application.