Before we go into the details of querying DataSets and DataTables, we have to fill the DataSet with some data. One of the basic ways of filling data in DataSet in ADO.NET is by using DataAdapter. Following is the code for loading data from the Categories and Items tables in the Deserts database, which we have already created:

//SQL Connection
SqlConnection conn = new SqlConnection
("Data Source=(local);Database=Deserts;Integrated Security=SSPI;");
//create Data Adapters
SqlDataAdapter categoriesAdapter = new SqlDataAdapter();
SqlDataAdapter itemsAdapter = new SqlDataAdapter();
// Create Command objects
SqlCommand categoriesCommand = new SqlCommand("Select * from
Categories", conn);
categoriesCommand.CommandType = CommandType.Text;
SqlCommand itemsCommand = new SqlCommand("Select * from
Items", conn);
itemsCommand.CommandType = CommandType.Text;
//Table mappings for Adapter
categoriesAdapter.TableMappings.Add("tableCategories", "Categories");
itemsAdapter.TableMappings.Add("tableItems", "Items");
// Set the DataAdapter's SelectCommand.
categoriesAdapter.SelectCommand = categoriesCommand;
itemsAdapter.SelectCommand = itemsCommand;
// Fill the DataSet.
categoriesAdapter.Fill(dataSetDeserts, "tableCategories");
itemsAdapter.Fill(dataSetDeserts, "tableItems");

The loading of data into DataSets can also be done using LINQ queries. For example, following is the code which loads the data from the Categories table into DataSet's DataTable. First, let us define Connection, DataTable, and entity classes to hold DataRows, and define the entity structure. Entity classes are not shown here as it is similar to the one discussed in LINQ to SQL Chapter.

Deserts db = new Deserts(@"C:demoLINQToDataSetsDeserts.mdf");
DataSet dataSet = new DataSet();
DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn();
dc1.DataType = System.Type.GetType("System.Int32");
dc1.Caption = "CategoryID";
dt.Columns.Add(dc1);
DataColumn dc2 = new DataColumn();
dc2.DataType = System.Type.GetType("System.String");
dc2.Caption = "CategoryName";
dt.Columns.Add(dc2);
DataColumn dc3 = new DataColumn();
dc3.DataType = System.Type.GetType("System.String");
dc3.Caption = "Description";
dt.Columns.Add(dc3);

Now write the LINQ query to fetch information from Categories using the Categories entity class.

var query = (from c in db.Categories
select new { c.CategoryID, c.CategoryName, c.Description});

Now execute the query and loop through the result and add the DataRows to the DataTable we defined earlier.

foreach (var result in query)
{
dt.Rows.Add(new object[] { result.CategoryID, result.CategoryName,
result.Description });
}
dataSet.Tables.Add(dt);
int count = dataSet.Tables[0].Rows.Count;
Console.WriteLine(" Number of Categories :" + count);

DataSet comes with a visualizer, to visualize the tables in DataSet, and DataRows within DataTables. For example, following is the visualization of DataRows in DataTables of DataSet, loaded using DataAdapter.

You can see DataTables listed in the DataSet Visualizer. On selecting DataTable, DataRows are listed in the grid that is shown in the visualizer. This is another way of verifying DataSet content.

LINQ provides many query operators and custom operators with which we can query DataSets. When we say querying DataSets, we actually mean querying DataTables inside DataSets. We cannot directly query DataTables, as it returns DataRow objects. To be a part of LINQ queries, DataTables should be enumerable and the source for the LINQ query should be IEnumerable<T>. Querying can be done on enumeration of DataRow objects so that we will have all DataColumns available for the query expressions.

var categories = dataSetDeserts.Tables[0].AsEnumerable();
var items = dataSetDeserts.Tables[1].AsEnumerable();
var rowCategories = from p in categories
where p.Field<int>("CategoryID") == 1
select p;
foreach (var cat in rowCategories)
{
Console.WriteLine(cat[0] + " " + cat[1] + " " + cat[2]);
}

In the above example, dataSetDeserts has two tables which have details of different categories and items for each category. To query these details, we need to get the enumeration of data rows from these tables. LINQ queries work on sources which are IEnumerable<>. The new ADO.NET provides a feature for getting the rows enumerated by applying AsEnumerable() on DataTables. Then we can write queries based on enumeration of DataRows. The query then uses the enumerable DataRow object categories and retrieves the records for the category, which has CategoryID equal to 1. Using Categories, the value of the each field is fetched and displayed in the list box. Field<> method, which avoids casting is used here to access CategoryID field. We can also use the column accessor to fetch column values from the data row, but it requires casting of the columns to return values. The Field accessor is a generic method, which avoids casting, and supports null able types.

Following is another example of a query which involves two data tables with a join:

var rowItemCategories = 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")
};
foreach (var itmcat in rowItemCategories)
{
Console.WriteLine("ItemID:" + itmcat.itemID + " Category:" +
itmcat.category + " Name:" + itmcat.itmName);
}

The join operator used in the previous query, to fetch details from two different tables by relating a column in each table, can be avoided by introducing a relation between the tables in DataSet itself. This is shown in the following code:

// Data Relation
DataRelation CatItem = new DataRelation("CategoryItems", dataSetDeserts.Tables[0].Columns["CategoryID"], dataSetDeserts.Tables[1].Columns["CategoryID"], true);
dataSetDeserts.Relations.Add(CatItem);
//Now try to fetch the records as below
foreach (var cat in rowCategories)
{
foreach (var item in cat.GetChildRows("CategoryItems"))
{
Console.WriteLine("ItemID:" + item["IItemID"] + " Category:" +
cat["CategoryName"] +" Name:" + item["Name"]);
}
}

The rowCategories is the same query used in the earlier single table query methods. The first foreach loop is to loop through each category in the Categories table. The second loop refers to the Detail table which is related to the main table used in the query and fetches the Detail table records also. This can be obtained by executing the GetChildrows method on the main query. Then, we can refer to any of the columns in the main query as well as the corresponding records in the Detail table. The GetChildRows method uses the name of the foreign key relation created between the tables. The CategoryItems is the name that corresponds to the relation between the Categories and Items tables.

We can also use sequence operator to replace the above queries. For example, we can have the following query, which produces the sequence for categories:

// Sequence
var categoriesDetails = categories.Select(n => new
{
CategoryID = n.Field<int>("CategoryID"),
Category = n.Field<string>("CategoryName"),
Description = n.Field<string>("Description")
});
foreach (var categoryDetails in categoriesDetails)
{
Console.WriteLine("CategoryID:" + categoryDetails.CategoryID + " Category:" + categoryDetails.Category + " Description:" +
categoryDetails.Description);
}

We can also apply the sequence on joins between tables. For example, following is an equivalent query for the join query we saw earlier.

// Sequence on Joins
var rowItmCategories = categories.Where(cat => cat.Field<int>("CategoryID") == 1)
.SelectMany(cat => cat.GetChildRows("CategoryItems")
.Select(itms =>new
{
itemID = itms.Field<int>("IItemID"),
categoryType = itms.Field<string>("CategoryType"),
itmName = itms.Field<string>("Name")
}));
foreach (var rowItemcats in rowItmCategories)
{
Console.WriteLine("itemID:" + rowItemcats.itemID + " Category
Type:" +
rowItemcats.categoryType + " ItemName:" + rowItemcats.itmName);
}

The structure of DataSets is similar to that of a relational database; it exposes a hierarchical object model of tables, rows, columns, constraints, and relationships. Typed DataSets derive the schema, which is the structure of the tables and columns, and are easier to program. An un-typed DataSet has no corresponding schema. Un-typed DataSets also contain tables, rows, and columns, but they are exposed as collections. Typed DataSet class has an object model in which its properties take on the actual names of the tables and columns. If we are working with typed DataSets, we can refer to a column directly as follows:

var categoryID = dataSetDeserts.Tables[0].CategoryID;

The following query uses un-typed DataSets:

var itemCategories = 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")
};
foreach (var itmcat in itemCategories)
{
Console.WriteLine("ItemID:" + itmcat.itemID + " Category:" +
itmcat.category + " Name:" + itmcat.itmName);
}

If it is a typed DataSet, the previous query is written as follows:

var rowItemCategories = from cats in categories
join item in items
on cats.CategoryID equals
item.CategoryID
where cats.CategoryID == 1
select new
{
itemID = item.IItemID,
category = cats.CategoryName,
itmName = item.Name
};
foreach (var itmcat in rowItemCategories)
{
Console.WriteLine("ItemID:" + itmcat.itemID + " Category:"
+ itmcat.category + " Name:" + itmcat.itmName);
}

With this query, we can avoid referencing the column using a field and casting it to the type of the database column. We can directly refer to a column in the table using the database column name.

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. It is just another source of data for LINQ, similar to an XML data source. 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.

Some of the DataSet query operators used, are explained in the following sections.

var items = dataSetDeserts.Tables[1].AsEnumerable();
var query = from item in items
select item;
DataTable results = query.CopyToDataTable();

//LoadDataRow
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);
}

// 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]);
}

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]);
}

var tblcategoriesMainExcept = tblcategoriesIntersect.Except(dtIntersect.AsEnumerable(), DataRowComparer.Default);
foreach (var rows in tblcategoriesMainExcept)
{
Console.Writeline("CategoryID:" + rows[0] + " ItemCategory:" +
rows[1] + " Description:" + rows[2]);
}

var rowItemsCategories = from cats in categories
join item in items
on cats.Field<int>("CategoryID") equals
item.Field<int>("CategoryID")
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")
};

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")
};

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

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 })
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.

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.

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);
}

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.

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);

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);

Apart from the operators covered so far in this chapter, there are many other operators which can be applied on DataTables and DataSets for querying, such as SelectMany(), Reverse(), Sum(), ToList(), TakeWhile(), and so on.

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();

In this chapter, we saw different ways of taking advantage of LINQ to query DataRows in typed, as well as un-typed, DataSets. We have also seen different DataSet‑specific query operators that make it easier to query DataRow objects. Some of these operators are not only used for comparing a sequence of rows, but also for accessing the column values of DataRows. In addition, we have seen some of the queries used for querying a single table in a DataSet, as well as multiple tables. So LINQ to DataSet makes it easier and faster to query cached data. Queries are not represented as string literals in the code; instead, they are the programming language itself. LINQ also provides compile time syntax checking, static typing and IntelliSense support, which increases a developer's