Linq to Dataset
Linq to Dataset is allows us to retrieve data from the Dataset. Since Dataset belongs to ADO.Net so we can query any kind database which is supported by ADO.Net. Linq can query any kind of Data Source which is implements <IEnumerable>, but by default Data Table does not implements the <IEnumerable>, so we should call AsEnumerable() Method, which is an extension method for DataTable, to obtain a wrapper that implements that interface. So that we need to add additional references with System.Linq.dll, System.Data.dll called System.Linq.DatasetExtension.dll.
Once the reference has been added we can query the Data Table using Linq. Let see the first example
//Creating clone to DataTable
dt = ds.Tables[0].Clone();
var dt = (from data in ds.Tables[0].AsEnumerable()
select data).CopyToDatatable();
dataGridView1.DataSource = dt;
//Lamda Expression
var dt = ds.Tables[0].AsEnumerable().Select(o => o).CopyToDataTable();
In the first line we just copy the Table schema to the new table. Please refer the Clone () Method. In the second line we are making the Table as Enumerable so that we can query the table. Then we are calling CopyToDataTable () method, provided as an extension of the IEnumerable<T> interface, is used to fill a typed DataTable object. Next we are binding the DataTable to DataGridView. In this query we are just pulling the data from one DataTable to another DataTable using Linq.
Where clause
Where clause is used to filter the data by checking the condition of fields or columns. I have used EmployeeID to check the condition.
dt = (from data in ds.Tables[0].AsEnumerable()
where data.Field<int>("EmployeeID") == 1
select data).AsDataView().ToTable();
//Lamda Expression
var dt = ds.Tables[0].AsEnumerable().Where(o => o.Field<int>("EmployeeID") == 1)
.Select(o => o).CopyToDataTable();
We should supply the Field type and column name implicity. Here why I am using .AsDataView().ToTable() instead of .CopyToDatatable() because if there no matching records then it will show an exception. I think it’s a bug.
Select Particular Fields
Normally we are fetching all rows from the tables but many times we need to fetch particular column from table .
for example here I have selected LastName,FirstName,Address,City columns only.
var dt = (from data in ds.Tables[0].AsEnumerable()
where data.Field<int>("EmployeeID") == 1
select new
{
LastName = data.Field<string>("LastName"),
FirstName = data.Field<string>("FirstName"),
Address = data.Field<string>("Address"),
City = data.Field<string>("City")
}).ToList();
//Lamda Expression
var dt = ds.Tables[0].AsEnumerable().
Where(o => o.Field<int>("EmployeeID") == 1)
.Select(r => new
{
LastName = r.Field<string>("LastName"),
FirstName = r.Field<string>("FirstName"),
Address = r.Field<string>("Address"),
City = r.Field<string>("City"),
}).ToList();
Here I have used var type datatype because we do not know the schema of Data Table. And also we can iterate the data like this.
foreach (var a in dt)
{
string LastName = a.LastName;
string FirstName = a.FirstName;
string Address = a.Address;
string City = a.City;
}
Order By Clause
Order by used arrange the result set we can both ascending and descending
var dt = (from data in ds.Tables[0].AsEnumerable()
orderby data.Field<string>("LastName")
select new
{
LastName = data.Field<string>("LastName"),
FirstName = data.Field<string>("FirstName"),
Address = data.Field<string>("Address"),
City = data.Field<string>("City")
}).ToList();
//Lamda Expression
var dt = ds.Tables[0].AsEnumerable()
.Select(r => new
{
LastName = r.Field<string>("LastName"),
FirstName = r.Field<string>("FirstName"),
Address = r.Field<string>("Address"),
City = r.Field<string>("City"),
}).OrderBy(o => o.LastName).ToList();
It will fetch the data with order by LastName.
Skip
var dt = (from data in ds.Tables[0].AsEnumerable()
orderby data.Field<string>("LastName")
select new
{
LastName = data.Field<string>("LastName"),
FirstName = data.Field<string>("FirstName"),
Address = data.Field<string>("Address"),
City = data.Field<string>("City")
}).Skip(3).ToList();
//Lamda Expression
var dt = ds.Tables[0].AsEnumerable()
.Select(r => new
{
LastName = r.Field<string>("LastName"),
FirstName = r.Field<string>("FirstName"),
Address = r.Field<string>("Address"),
City = r.Field<string>("City"),
}).OrderBy(o => o.LastName).Skip(3).ToList();
It will skip first three record.
Group By
Group By clause is used to grouping the rows based on cloumns
var dt = (from data in ds.Tables[0].AsEnumerable()
group data by data.Field<string>("City") into Employee
select new
{
City= Employee.Key,
Count= Employee.Count()
}).ToList();
//Lamda Expression
var dt = ds.Tables[0].AsEnumerable().GroupBy(g => g.Field<string>("City")).
Select(r => new
{
City = r.Key,
Count = r.Count()
}).ToList();
It will group the Data by City and output will City and Count of each city.
Output will be
City | Count |
Seattle | 2 |
Tacoma | 1 |
KirkLand | 1 |
Redmond | 1 |
London | 4 |
Join Clause
Many times we need to select data from than one table so that we should go the join clause. Join clause is used to join the more than one table using keys like primary,foreign key etc….
var dt = (from product in ds.Tables["Products"].AsEnumerable()
join order in ds.Tables["OrderDetails"].AsEnumerable()
on product.Field<int>("ProductID") equals order.Field<int>("ProductID")
where product.Field<int>("ProductID") == 11
select new
{
ProductName = product.Field<string>("ProductName"),
SupplierID = product.Field<int>("SupplierID").ToString(),
UnitPrice = order.Field<decimal>("UnitPrice").ToString(),
Quantity = order.Field<int>("Quantity").ToString()
}).ToList();
//Lamda Expression
var dt = ds.Tables["Products"].AsEnumerable().Join(
ds.Tables["OrderDetails"].AsEnumerable(),
r => r.Field<int>("ProductID"),
o => o.Field<int>("ProductID"),
(r, o) => new { Product = r, Order = o }).Where(n => n.Product.Field<int> ("ProductID") == 11)
.Select(p => new
{
ProductName = p.Product.Field<string>("ProductName"),
SupplierID = p.Product.Field<int>("SupplierID").ToString(),
UnitPrice = p.Order.Field<decimal>("UnitPrice").ToString(),
Quantity = p.Order.Field<int>("Quantity").ToString()
}).ToList();
Joining two is like how you joining Sql here I have join Product with OrderDetails on Primary key called ProductID and conditioned with ProductID is 11.It will do inner join from the result I have selected only four columns. Beware while you using datatype inside Field<> because wrong datatype will throw an exception.
Distinct
The Distinct operator removes duplicate rows from a sequence of objects. It returns an object that, when enumerated, enumerates a source sequence of objects and returns a sequence of objects with the duplicate rows removed.
var dt = (from data in ds.Tables[0].AsEnumerable()
select new
{
LastName = data.Field<string>("LastName"),
FirstName = data.Field<string>("FirstName"),
Address = data.Field<string>("Address"),
City = data.Field<string>("City")
}).Distinct().ToList();
//Lamda Expression
var dt = ds.Tables[0].AsEnumerable()
.Select(r => new
{
LastName = r.Field<string>("LastName"),
FirstName = r.Field<string>("FirstName"),
Address = r.Field<string>("Address"),
City = r.Field<string>("City"),
}).Distinct().ToList();
Just call Distinct() to get distinct data from table.
Examples in the article is just a sample one. We can write more and more complex queries. So play with Linq queries.
I hope this article will be very helpful to all. Thanks for reading this article.
“Keep reading and share the knowledge”
“Grow more trees to save the Earth”
IEnumerable query = from c in dt.AsEnumerable()
ReplyDeletejoin o in dt1.AsEnumerable() on c.Field("Row_Index") equals o.Field("Row_Index")
select new
{
Row_Index = c.Field("Row_Index"),
Revision = o.Field("Revision"),
Column_ID = c.Field("Column_ID"),
Module_ID = c.Field("Module_ID"),
Stack_ID = c.Field("prodid"),
newStack_ID = o.Field("prodid")
};
DataTable boundTable = query.CopyToDataTable();
Error is
Error 40 Cannot implicitly convert type 'System.Collections.Generic.IEnumerable' to 'System.Collections.Generic.IEnumerable'. An explicit conversion exists (are you missing a cast?)