Home > Linq > LINQ # 7 – LINQ to Dataset (Part II) (DATASET QUERY OPERATORS)


Hi Geeks,

As we see in our last post LINQ # 6 – LINQ To Dataset (Part–I) how to fire a query against dataset.

Today we will see the DATASET QUERY OPERATORS

List of dataset query operators :-

* CopyToDataTable
* LoadDataRow
* Intersect
* Union
* Except
* Field<T>
* SetField<T>
* Projection
* Join
* SequenceEqual
* Skip
* Distinct

* LINQ to DataSet adds several DataSet-specific operators to the standard query operators available in System.core.dll. This is to make DataSet query capabilities easier. Once DataSets are loaded with data, we can begin querying them just as we do against the database tables using database queries.

* We can query a single table or multiple tables in a DataSet using join and groupby operators. If the schema of DataSet is known at the application design time, we can use typed DataSet for the queries which will be easier and will be more readable

**  CopyToDataTable

This operator is used for creating a new DataTable from the query. The properties are taken as DataColumns and the field values are iterated and converted as data values for the columns. Following is the query which refers to dataSetDeserts in the Items table in the DataSet.

 var items = dataSetDeserts.Tables[1].AsEnumerable();
var query = from item in items
select item;
DataTable results = query.CopyToDataTable();  the Items table in the DataSet. CopyToDataTable operator is applied on the query to convert it to a DataTable.


**  LoadDataRow

This operator adds DataRows to the existing DataTable. The following query iterates through the Categories table and adds rows one-by-one to a new DataTable which has DataColumns of the same type. This operator takes two parameters. The first one is an object that is a collection of DataColumns, and the second parameter is boolean for accepting the changes.

var itemrows = dataSetDeserts.Tables[1].AsEnumerable();
var rowItems = from p in itemrows
where p.Field<int>("CategoryID") == 1
select new Items { IItemID = p.Field<int>("IItemID"), Name =
p.Field<string>("Name"), Ingredients = p.Field<string>("Ingredients") };
DataTable dt = new DataTable("TestTable");
dt.Columns.Add(new DataColumn("IItemID", typeof(int)));
dt.Columns.Add(new DataColumn("Name", typeof(string)));
dt.Columns.Add(new DataColumn("Ingredients", typeof(string)));
foreach (var row in rowItems)
dt.LoadDataRow(new object[] { row.IItemID, row.Name,
row.Ingredients }, true);

** Intersect

The Intersect operator produces an intersection of sequence of two different sets of DataRows. It returns enumerable DataRows. Following is an example of the Intersect operator. The first DataTable, tblcategoriesIntersect, intersects with the second table, tblcategoriesMain, and returns the common DataRows of the two DataTables. The first DataTable, dtIntersect, takes the enumerable data rows of Categories from the dataSetDeserts DataSet, which we created at the beginning of this chapter. The Intersect operator takes the distinct enumerable DataRows from the source DataTable and then iterates through the second set of DataRows and compares them one-by-one. The comparison is done on the number of DataColumns and their types. The second parameter is the compare option for intersecting DataRows.
// To retrive rows which are common in both the tables
DataTable dtIntersect = new DataTable("TestTable");
dtIntersect.Columns.Add(new DataColumn("CategoryID", typeof(int)));
dtIntersect.Columns.Add(new DataColumn("CategoryName", typeof(string)));
dtIntersect.Columns.Add(new DataColumn("Description", typeof(string)));
var drIntersect = new { CategoryID = 1, CategoryName = "Icecream",
 Description = "Icecreams Varieties" };
dtIntersect.Rows.Add(new object[] { drIntersect.CategoryID, drIntersect.CategoryName, drIntersect.Description });
var tblcategoriesIntersect = dataSetDeserts.Tables[0].AsEnumerable();
var tblcategoriesMain =
tblcategoriesIntersect.Intersect(dtIntersect.AsEnumerable(), DataRowComparer.Default);
foreach (var rows in tblcategoriesMain)
Console.WriteLine("CategoryID:" + rows[0] + " ItemCategory:" +
rows[1] + " Description:" + rows[2]);

** Union

The Union operator returns the union of two different sequences of DataRows. The operator first yields the first sequence of DataRows and then the second sequence. It will yield the elements that are common to both only once. Following is an example of the Union operator; dtUnion is a new table with three columns, which is the same type as in the Categories table, retrieved from the dataSetDeserts DataSet we built at the beginning of this chapter. The dtUnion table has one DataRow added to it. The Union operator is applied on the categories1 DataTable with the new table created. The resultant table, categoriesUnion, is the union of both these tables.

DataTable dtUnion = new DataTable("TestTable");
dtUnion.Columns.Add(new DataColumn("CategoryID", typeof(int)));
dtUnion.Columns.Add(new DataColumn("CategoryName", typeof(string)));
dtUnion.Columns.Add(new DataColumn("Description", typeof(string)));
var catsNew = new { CategoryID = 5, Category = "NewCategory",
Description = "NewDesertType" };
dtUnion.Rows.Add(new object[] { catsNew.CategoryID, catsNew.Category, catsNew.Description });
var categories1 = dataSetDeserts.Tables[0].AsEnumerable();
var categoriesUnion = categories1.Union(dtUnion.AsEnumerable(), DataRowComparer.Default);
foreach (var row in categoriesUnion)
Console.WriteLine("CategoryID:" + row[0] + " ItemCategory:" + row[1]
+ " Description:" + row[2]);
** Except
The Except operator produces non-common DataRows from two different sets of sequences of DataRows. It is the exact opposite of the Intersect operator. This operator first takes distinct rows from the first sequence, then enumerates over DataRows of the second sequence and compares with the first result. It eliminates the rows that are common to both the sequences. The following code is an example of the Except operator.
var tblcategoriesMainExcept = tblcategoriesIntersect.Except(dtIntersect.AsEnumerable(), DataRowComparer.Default);
foreach (var rows in tblcategoriesMainExcept)
Console.Writeline("CategoryID:" + rows[0] + " ItemCategory:" +
rows[1] + " Description:" + rows[2]);
In next blog we will see rest of the dataset query operators.
Categories: Linq
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: