How to use Datasets with Linq in Asp.net

Define:

             Linq to Datasets provides a runtime infrastructure for querying Datable in Datasets as object without losing ability to querying. Linq to Data ships in .Net Framework 4.0.  Lin treats dataset as a Collection of columns which hold relational data. Let’s start with Simple Linq to dataset query and further in deep functionality of query.

Data:

First we will be having data in two data tables which resides in function GetEmployee() and GetDepartment(). Below is brief look at two data tables and structure.

Employee Table

Department Table

These datatables can be further added to datasets in the following ways.

DataSet ds = new DataSet();

       ds.Tables.Add(getEmployee());

       ds.Tables[0].TableName = "Emp";

       ds.Tables.Add(getDapartment());

       ds.Tables[1].TableName = "Dept";

We can even define Foreign key relation between two tables in dataset.

                DataColumn[] dc;

       dc = new DataColumn[1];

dc[0] = ds.Tables["Dept"].Columns["DeptID"];

       ds.Tables["Dept"].PrimaryKey = dc;

ForeignKeyConstraint fkcDept = new ForeignKeyConstraint(ds.Tables["Dept"].Columns["DeptID"],

ds.Tables["Emp"].Columns["DeptID"]);

       ds.Tables["Emp"].Constraints.Add(fkcDept);

Select Linq Query on Dataset

Employee Table

var query = (from a in ds.Tables["Emp"].AsEnumerable()

       select new

       {

EmpId = a["EmpId"],

              Name = a["Name"],

              Salary = a["Salary"],

});

gvData.DataSource = query;

       gvData.DataBind();

Result

Department Table

var query = (from a in ds.Tables["Dept"].AsEnumerable()

       select new

       {

DeptId = a["DeptId"],

              Name = a["DeptName"],

});

gvData.DataSource = query;

       gvData.DataBind();

Result

Select Join Linq Query on Dataset

var query = (from a in ds.Tables["Emp"].AsEnumerable()

join b in ds.Tables["Dept"].AsEnumerable()

on a.Field<Int32>("DeptId") equals b.Field<Int32>("DeptId")

              select new

              {

                     EmpId = a["EmpId"],

                     Name = a["Name"],

                     DeptName = b["DeptName"],

                     Salary = a["Salary"],

});

Result

Where Linq Query on Dataset

var query = (from a in ds.Tables["Emp"].AsEnumerable()

join b in ds.Tables["Dept"].AsEnumerable()

on a.Field<Int32>("DeptId") equals b.Field<Int32>("DeptId")

       where b.Field<String>("DeptName") == "Information Technology"

       && a.Field<Decimal>("Salary") > 15000

       select new

       {

              EmpId = a["EmpId"],

              Name = a["Name"],

              DeptName = b["DeptName"],

              Salary = a["Salary"],

});

Result