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.
int[] numbers = new int[7] { 0, 1, 2, 3, 4, 5, 6 };
var numQuery =
from num in numbers
where (num % 2) == 0
select num;
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.
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 or ToArray methods.
List<int> numQuery2 =
(from num in numbers
where (num % 2) == 0
select num).ToList();
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).
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.
