LINQ to Dataset in C#.NET
A DataSet is a representation of the tables and from any data source(for example Database or XML file) datasource, exposing a hierarchical object model made of all the objects such as tables, rows, columns, constraints, and relationships. Most of the functionality that populates the DataSet and saves the changes within the DataSet back to the datasource is found in ADO.NET.
Even some times you may have requirement to query the dataset. In this article we discuss about how to write a LINQ query on dataset by using Query Syntax and Method Syntax. Dataset is a collection of data tables, so we have to create the LINQ query on particular data table. First fetch the data table from dataset and write the LINQ query on that data table.
We can write the LINQ query on Dataset data table by using both Query Syntax and Method Syntax. To write LINQ query on any object it has to be Enumerable. So first we have to convert Dataset data table to enumerable object then qrite query on that enumerable object.
LINQ Query on Dataset data table by using Query Syntax:
var emp = ( from row in dt.AsEnumerable()
where row["Name"].ToString().StartsWith("D")
select new { Id = (string)row["Id"], Name = (string)row["Name"] }
);
LINQ Query on Dataset data table by using Method Syntax:
var emp = dt.AsEnumerable().Where(e1 => e1["Name"].ToString().StartsWith("D")).Select(e1 => new { Id
= (string)e1["Id"], Name = (string)e1["Name"] });
Here we have XML file as a data sourec for Dataset and from dataset we are getting the data table then finally we write the LINQ query on particular datatable to fetch the records which Name start with letter “D” as shown below. The result will display by using Gridview control.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>LINQ On Dataset</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gv1" runat="server"></asp:GridView>
</div>
</form>
</body>
</html>
using System;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("Employee.xml"));
DataTable dt = ds.Tables[0];
//By using Query Syntax
//var emp = (from row in dt.AsEnumerable()
// where row["Name"].ToString().StartsWith("D")
// select new { Id = (string)row["Id"], Name = (string)row["Name"] });
//By use Method Syntax
var emp = dt.AsEnumerable().Where(e1 => e1["Name"].ToString().StartsWith("D")).Select(e1 => new { Id = (string)e1["Id"], Name = (string)e1["Name"] });
gv1.DataSource = emp;
gv1.DataBind();
}
}
The output displays only one record from XML datasource which starts with letter “D” as shown below.
