Queries in LINQ to DataSet

.NET Framework 4.6 and 4.5

 A query is an expression that retrieves data from a data source. Queries are usually expressed in a specialized query language, such as SQL for relational databases and XQuery for XML. Therefore, developers have had to learn a new query language for each type of data source or data format that they query. Language-Integrated Query (LINQ) offers a simpler, consistent model for working with data across various kinds of data sources and formats. In a LINQ query, you always work with programming objects.

A LINQ query operation consists of three actions: obtain the data source or sources, create the query, and execute the query.

Data sources that implement the IEnumerable<T> generic interface can be queried through LINQ. Calling AsEnumerable on a DataTablereturns an object which implements the generic IEnumerable<T> interface, which serves as the data source for LINQ to DataSet queries.

In the query, you specify exactly the information that you want to retrieve from the data source. A query can also specify how that information should be sorted, grouped, and shaped before it is returned. In LINQ, a query is stored in a variable. If the query is designed to return a sequence of values, the query variable itself must be a enumerable type. This query variable takes no action and returns no data; it only stores the query information. After you create a query you must execute that query to retrieve any data.

In a query that returns a sequence of values, the query variable itself never holds the query results and only stores the query commands. Execution of the query is deferred until the query variable is iterated over in a foreach or For Each loop. This is called deferred execution; that is, query execution occurs some time after the query is constructed. This means that you can execute a query as often as you want to. This is useful when, for example, you have a database that is being updated by other applications. In your application, you can create a query to retrieve the latest information and repeatedly execute the query, returning the updated information every time.

In contrast to deferred queries, which return a sequence of values, queries that return a singleton value are executed immediately. Some examples of singleton queries are Count, Max, Average, and First. These execute immediately because the query results are required to calculate the singleton result. For example, in order to find the average of the query results the query must be executed so that the averaging function has input data to work with. You can also use the ToList<TSource> or ToArray<TSource> methods on a query to force immediate execution of a query that does not produce a singleton value. These techniques to force immediate execution can be useful when you want to cache the results of a query

Queries

LINQ to DataSet queries can be formulated in two different syntaxes: query expression syntax and method-based query syntax.

Query Expression Syntax

Query expressions are a declarative query syntax. This syntax enables a developer to write queries in C# or Visual Basic in a format similar to SQL. By using query expression syntax, you can perform even complex filtering, ordering, and grouping operations on data sources with minimal code. For more information, see LINQ Query Expressions (C# Programming Guide) and Basic Query Operations (Visual Basic).

Query expression syntax is new in C# 3.0 and Visual Basic 2008. However, the .NET Framework common language runtime (CLR) cannot read the query expression syntax itself. Therefore, at compile time, query expressions are translated to something that the CLR does understand: method calls. These methods are referred to as the standard query operators. As a developer, you have the option of calling them directly by using method syntax, instead of using query syntax. For more information, see Query Syntax and Method Syntax in LINQ (C#). For more information about how to use the standard query operators, see NOT IN BUILD: LINQ General Programming Guide.

The following example uses Select<TSource, TResult> to return all the rows from Product table and display the product names.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable products = ds.Tables["Product"];

IEnumerable<DataRow> query =
    from product in products.AsEnumerable()
    select product;

Console.WriteLine("Product Names:");
foreach (DataRow p in query)
{
    Console.WriteLine(p.Field<string>("Name"));
}

Method-Based Query Syntax

The other way to formulate LINQ to DataSet queries is by using method-based queries. The method-based query syntax is a sequence of direct method calls to LINQ operator methods, passing lambda expressions as the parameters. For more information, see Lambda Expressions (C# Programming Guide).

This example uses Select<TSource, TResult> to return all the rows from Product and display the product names.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable products = ds.Tables["Product"];

var query = products.AsEnumerable().
    Select(product => new
    {
        ProductName = product.Field<string>("Name"),
        ProductNumber = product.Field<string>("ProductNumber"),
        Price = product.Field<decimal>("ListPrice")
    });

Console.WriteLine("Product Info:");
foreach (var productInfo in query)
{
    Console.WriteLine("Product name: {0} Product number: {1} List price: ${2} ",
        productInfo.ProductName, productInfo.ProductNumber, productInfo.Price);
}

Composing Queries

As mentioned earlier in this topic, the query variable itself only stores the query commands when the query is designed to return a sequence of values. If the query does not contain a method that will cause immediate execution, the actual execution of the query is deferred until you iterate over the query variable in a foreach or For Each loop. Deferred execution enables multiple queries to be combined or a query to be extended. When a query is extended, it is modified to include the new operations, and the eventual execution will reflect the changes. In the following example, the first query returns all the products. The second query extends the first by usingWhere to return all the products of size "L":

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable products = ds.Tables["Product"];

IEnumerable<DataRow> productsQuery =
    from product in products.AsEnumerable()
    select product;

IEnumerable<DataRow> largeProducts =
    productsQuery.Where(p => p.Field<string>("Size") == "L");

Console.WriteLine("Products of size 'L':");
foreach (DataRow product in largeProducts)
{
    Console.WriteLine(product.Field<string>("Name"));
}

After a query has been executed, no additional queries can be composed, and all subsequent queries will use the in-memory LINQ operators. Query execution will occur when you iterate over the query variable in a foreach or For Each statement, or by a call to one of the LINQ conversion operators that cause immediate execution. These operators include the following: ToList<TSource>,ToArray<TSource>, ToLookup, and ToDictionary.

In the following example, the first query returns all the products ordered by list price. The ToArray<TSource> method is used to force immediate query execution:

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable products = ds.Tables["Product"];

IEnumerable<DataRow> query =
    from product in products.AsEnumerable()
    orderby product.Field<Decimal>("ListPrice") descending
    select product;

// Force immediate execution of the query.
IEnumerable<DataRow> productsArray = query.ToArray();

Console.WriteLine("Every price from highest to lowest:");
foreach (DataRow prod in productsArray)
{
    Console.WriteLine(prod.Field<Decimal>("ListPrice"));
}

You may also like :

Basic steps to implement ASP dotnet MVC using Entity Framework Code First approach
SQL Query to Release lock on database
How to change URL withought page postback
Csharp Code to make call By connecting you phone to PC
BE Electronics ETC IT Computer Project Titles - Group 1
SQL Query to Split string by specific seperator
Simple Page Method in asp.net
Error 720 Resolution
Queries in LINQ to DataSet
BEWARE-XSS THIEVES ARE LOOKING FOR YOU
how to search date within range in c Sharp dot net and mssql