LINQ系列：LINQ to DataSet的DataTable
LINQ to DataSet需要使用System.Core.dll、System.Data.dll和System.Data.DataSetExtensions.dll，在项目中添加引用System.Data和System.Data.DataSetExtensions。
1. DataTable读取列表
DataSet ds =newDataSet();//省略ds的Fill代码
DataTable products = ds.Tables["Product"];
IEnumerable rows =from p in products.AsEnumerable()
select p;
foreach(DataRow row in rows)
{
Console.WriteLine(row.Field<string>("ProductName"));
}
DataSet ds =newDataSet();//省略ds的Fill代码
DataTable products = ds.Tables["Product"];
var rows =products.AsEnumerable()
.Select(p=>new{
ProductID= p.Field<int>("ProductID"),
ProductName= p.Field<string>("ProductName"),
UnitPrice= p.Field<decimal>("UnitPrice")
});
foreach(var row in rows)
{
Console.WriteLine(row.ProductName);
}
var products = ds.Tables["Product"].AsEnumerable();
var query =from p in products
select p.Field<string>("ProductName");
2. DataTable查询
var rows =products.AsEnumerable()
.Where(p=> p.Field<decimal>("UnitPrice") >10m)
.Select(p=>new{
ProductID= p.Field<int>("ProductID"),
ProductName= p.Field<string>("ProductName"),
UnitPrice= p.Field<decimal>("UnitPrice")
});
3. DataTable数据排序
var rows =products.AsEnumerable()
.Where(p=> p.Field<decimal>("UnitPrice") >10m)
.OrderBy(p=> p.Field<string>("SortOrder"))
.Select(p=>new{
ProductID= p.Field<int>("ProductID"),
ProductName= p.Field<string>("ProductName"),
UnitPrice= p.Field<decimal>("UnitPrice")
});
var expr =from p in products.AsEnumerable()
order by p.Field<int>("SortOrder")
select p;
IEnumerable rows =expr.ToArray();
foreach(var row in rows)
{
Console.WriteLine(row.Field<string>("ProductName"));
}
var expr =from p in ds.Tables["Product"].AsEnumerable()
orderby p.Field<int>("SortOrder"), p.Field<string>("ProductName") descending
select p;
4. 多个DataTable查询
var query =from p in ds.Tables["Product"].AsEnumerable()
from c in ds.Tables["Category"].AsEnumerable()
where p.Field<int>("CategoryID") == c.Field<int>("CategoryID") &&
p.Field<decimal>("UnitPrice") >10m
select new{
ProductID= p.Field<int>("ProductID"),
ProductName= p.Field<string>("ProductName"),
CategoryName= c.Field<string>("CategoryName")
};
5. DataTable分组
var query =from p in ds.Tables["Product"].AsEnumerable()
group p by p.Field<int>("CategoryID") into g
select new{
CategoryID=g.Key,
Products=g
};
foreach(var item in query)
{
Console.WriteLine(item.CategoryID);
foreach(var p in item.Products)
{
Console.WriteLine(p.Field<string>("ProductName"));
}
}
查询Product中每个CategoryID的数目：
var expr =from p inds.Tables["Product"].AsEnumerable()
group p by p.Field<int>("CategoryID") into g
select new{
CategoryID=g.Key,
ProductsCount=g.Count()
};
6. 多个DataTable左连接
var linq1 = from a in dt1.AsEnumerable()
join b in dt2.AsEnumerable()
on a.Field<string>("sys_nation_id") equals b.Field<string>("sys_nation_id")
into ab
from bb in ab.DefaultIfEmpty()
select new {
proname = a.Field<string>("name"),
countryname = bb == null ? "" : bb.Field<string>("name")
};
若要实现三个表左连接查询：
var linq2 = from a in dt1.AsEnumerable()
join b in dt2.AsEnumerable()
on a.Field<string>("sys_nation_id") equals b.Field<string>("sys_nation_id")
into ab
from bb in ab.DefaultIfEmpty()
join c in dt3.AsEnumerable()
on a.Field<string>("sys_city_id") equals b.Field<string>("sys_city_id")
into bc
from cc in bc.DefaultIfEmpty()
select new {
proname = a.Field<string>("name"),
countryname = bb == null ? "" : bb.Field<string>("name"),
cityname = cc == null ? "" : cc.Field<string>("name")
};
