LINQ # 8 – LINQ to Dataset (Part III) (DATASET QUERY OPERATORS)

Hi Friends,

Continuing from my blog LINQ to Dataset (Part – II) Dataset Query Operators ,Today we will see the following Dataset Query Operators

*  Field<T>
* SetField<T>
* Projection
* Join
* SequenceEqual
* Skip
* Distinct

** Field<T>

When we query data for comparison, there could be a chance that the value is null. If we do not handle nulls when we retrieve data, we could end up getting exceptions. For example, following is the query for checking and handling nulls for the category description. The where clause checks for the category, and also checks if categoryID is not equal to null. The column value will be null if the column value is returned as DbNull from the database.

var rowItemsCategories = from cats in categories
join item in items
on cats.Field<int>("CategoryID") equals
item.Field<int>("CategoryID")
LINQ over DataSet
[ 152 ]
where (int)cats["CategoryID"] == 1
&& !cats.IsNull("CategoryID")
select new
{
itemID = item.Field<int>("IItemID"),
category = cats.Field<string>("CategoryName"),
itmName = item.Field<string>("Name")
};

 

Checking the null value of the column value can be avoided by using the Field operator. The Field method takes care of checking the null value of the column.

var rowsItemsCategories = from cats in categories
join item in items
on cats.Field<int>("CategoryID") equals
item.Field<int>("CategoryID")
where cats.Field<int>("CategoryID") == 1
select new
{
itemID = item.Field<int>("IItemID"),
category = cats.Field<string>("CategoryName"),
itmName = item.Field<string>("Name")
};

 

** SetField<T>

This method is used to set the value of DataColumns in DataRows. The advantage here is that we do not have to worry about null values in the DataSet.

public static void SetField ( this DataRow first,
System.Data.DataColumn column, T value);

** Projection

LINQ provides a select method for projecting each element of a sequence. Following is an example of the projection applied to the Categories table.

var tblCategories = db.Categories.AsEnumerable();
var qqry = tblCategories.Select(category => new { cID = category.
CategoryID, cCategory = category.Category, cDesc = category.Description })
OrderBy(e => e.cCategory);
foreach (var cats in qqry)
{
Console.WriteLine("Id:" + cats.cID + " Desc:" + cats.cDesc);
}

The query, qqry, is built by the projection operator on the Categories table. The select method projects DataColumn elements into a new form of DataRows. The OrderBy operator is applied on the Category DataColumn, which is responsible for ordering the resultant data rows.

 

** Join

This is an operator that joins the elements of two different sequences based on the matching keys. This is similar to the join operator that we have in database queries. The following example has two different tables, tblCategoriesforJoin and tblItemsforJoins having a common DataColumn. The join can be applied on the key column CategoryID of both the sequences.

A join is applied on the first DataTable. It takes four parameters: name of the other table, which participates in the join; outerKeySelector; innerKeySelector; and the actual result of the join operation.

var tblCategoriesforJoin = dataSetDeserts.Tables[0].AsEnumerable();
var tblItemsforJoins = dataSetDeserts.Tables[1].AsEnumerable();
var categoryItems = tblCategoriesforJoin.Join(tblItemsforJoins, o => o.Field<int>("CategoryID"), c => c.Field<int>
("CategoryID"),(c, o) => new
{
CategoryID = c.Field<int>("CategoryID"),
ItemID = o.Field<int>("IItemID"),
Name = o.Field<string>("Name")
});
foreach (var itm in categoryItems)
{
Console.WriteLine("CategoryID:" + itm.CategoryID + " ItemID:" +
itm.ItemID + " Name:" + itm.Name);
}

** SequenceEqual

This operator is used for comparing two different sequences. It returns a boolean value, which says yes or no. It takes only one argument, which is the second set of enumerable DataRows. Following is an example for checking the equality of two different sequences, tblCategoriesforJoin and tblItemsforJoins.

var categoryItems =
tblCategoriesforJoin.SequenceEqual(tblItemsforJoins);

 

** Skip

This operator is useful when we want to skip some of the rows from a DataTable. For example the following statement shows a way to skip the first two rows from the tblCategoriesforJoin table.

var categoryItems2 = tblCategoriesforJoin.Skip(2);

** Distinct

This Distinct operator produces a distinct set of rows from a given sequence of rows. It removes repeated rows from a set. The result is an enumerable DataTable which contains distinct DataRows from the source table. For example, the following code produces distinct rows from the Categories table. If it contains any duplicate rows, they will be removed and the resultant table will not have any duplication.

var distinctCategories = categories.Distinct();

 

We saw important Dataset Query Operators with that we also have many other datset query operators as

SelectMany(), Reverse(), Sum(), ToList(), TakeWhile(), and so on.

For more interesting links and daily update please subscribe our link resource website
    Todays link are follows:
Link Resource # 12 August 3 – August 4 « Dactylonomy of Web Resource

 

Thanks.