Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Linq to Dataset

Introduction:

Previously I written two articles on linq features LINQ to SQL and Linq to Objects, I specified in first article I will continue writing some more articles on LINQ features.

In this article I will describe in detail LINQ to Dataset in detail with some sample examples.

 What is LINQ to dataset?

LINQ to Dataset provides the easier and faster approach to query over data cached in a DataSet object.

LINQ to DataSet can also be used to query over data that has been consolidated from one or more data sources.

For example querying data on actual data and intermediate cached data source in financial web application, such scenarios required to create financial reports.

Following diagram explain relationship between LINQ to Dataset to ADO.NET 2.0 and Database.

                       

Why LINQ to Dataset?

LINQ to Dataset allows developers to write queries from the programming language itself, instead of using separate query language.

Programming query languages who can now take advantage of the compile-time syntax checking, static typing, and IntelliSense support provided by the Visual Studio in their queries,this is especially useful for Visual Studio developers.

Advantages using Linq to Dataset:

1. Easy and readable.

2. No need to learn separate query languages like SQL (Structured query language), you can use visual studio programming language.
3.  You can use linq on two different datasets or you can retrieve some distinct values from dataset using Linq To Dataset.
4. You can use Sql queries to retrieve dataset but you can’t use SQL queries to retrieve value from dataset, in classic programming .net provides ado.net objects but with Linq to Dataset approach you can retrieve some distinct values from dataset.

 LINQ to Dataset Single table Queries:

The following obtains valid Products from the “C4Test” sample database using LINQ to dataset query.

  DataSet ProductsData = ProductDataAccess.GetProductDetails();  
  DataTable Products = ProductsData.Tables["Products"];

 

          var ValidProducts =
              from product in Products.AsEnumerable()
              where product.Field<string>("ProductName") != string.Empty
              && product.Field<int>("CategoryId") != 0
              select new
              {
                  ID =
                      product.Field<int>("ID"),
                  Name =
                      product.Field<int>("ProductName"),
                  Price =
                      product.Field<int>("Price")
              };

 

LINQ to Dataset Cross table query Example:

The following example performs a classic join (SQL join) of the Products and Categories tables from the “C4Test” sample database to obtain valid product details with categories.
LINQ framework provides two join operators, Join and GroupJoin. These operators perform equi-joins: that is, joins that match two data sources only when their keys are equal.
DataSet ProductsData = ProductDataAccess.GetProductDetails();
DataSet CategoriiesData = ProductDataAccess.GetProductCategories();
 
DataTable Products = ProductsData.Tables["Products"];
DataTable Categories = CategoriiesData.Tables["Categories"];

 

           var query =
               from product in Products.AsEnumerable()
               join category in Categories.AsEnumerable()
               on product.Field<int>("CategoryId") equals
                   category.Field<int>("ID")
               where product.Field<string>("ProductName") != string.Empty
               && product.Field<int>("CategoryId") != 0
               select new
               {
                   ID =
                       product.Field<int>("ID"),
                   Name =
                       product.Field<int>("ProductName"),
                   Category =
                       category.Field<string>("CategoryName"),
                   Price =
                       product.Field<int>("Price")
               };

 

Creating a DataTable from a Query (LINQ to Dataset):

The following example queries the Categories table for valid categories and uses the CopyToDataTable method to create a DataTable from that query.
 DataSet CategoriiesData = ProductDataAccess.GetProductCategories();
 DataTable Categories = CategoriiesData.Tables["Categories"];

 

  // Query the Categories table for only valid categories
  IEnumerable<DataRow> ValidCategories =
           from category in Categories.AsEnumerable()
           where category.Field<string>("CategoryName")!=string.Empty
           select category;
 
   // create a table from the query.
   DataTable NewValidDataTable = ValidCategories.CopyToDataTable<DataRow>();

 

Conclusion:

LINQ to Dataset allows developers to write queries from the programming language itself, instead of using separate query language.

 You can perform joins on different tables and you can retrieve only required data from different data sources.

 

Invalid entry,please enter valid data.

Loading