LINQ To DataSet 示例

如果在项目遇到这样的问题如：DataTable1和DataTable2需要根据一定的规则进行合并成一个DataTable3。

问题1：DataTable1不是读数据库表的结果，而是合成的数据集,因此无法用SQL语句组合查询。

问题2：DataTable1与DataTable2本身就是非常繁琐的查询且结果集非常大，这样如果DataTable1再与DataTable2

　　 　组合查询则很容易发生SQL执行超时。

遇到以上问题，第一个想法就是把两个DataTable，取出放至内存中用嵌套遍历的方式重组得到DataTable3.

.net推出LINQ后就可以用这种更简洁易懂的类SQL语法的LINQ To DataTable 来解决以上问题。下面我例出几个示例分享。

using System.Linq;
using System.Text;
using System.Data;

1.基本方法：

　DataSet ds = new DataSet();
   adapter.Fill(ds, "Customers");//读数据库略

   var result = from s1 in ds.Tables["Customers"].AsEnumerable()

                                 where s1.Field<string>("Region") == "SP"   //转换成string后进行对比，如果为DBNull 则传回空字符串
                                 select s1;

   foreach (var item in result)
           Console.WriteLine(item["CustomerID"]);
   Console.ReadLine();

2.指定DataRowVersion:

var result = from s1 in ds.Tables["Customers"].AsEnumerable()
                                 where !s1.IsNull("Region") && (string)s1["Region"] == "SP"
                                 select s1;

3. Join组合查询：

 var result = from s1 in ds.Tables["Customers"].AsEnumerable()
                                 join s2 in ds.Tables["Orders"].AsEnumerable() on
                                 s1.Field<string>("CustomerID") equals s2.Field<string>("CustomerID")
                                 where s2.Field<DateTime>("OrderDate") > DateTime.Parse("1997/1/1") &&
                                       s2.Field<DateTime>("OrderDate") < DateTime.Parse("1997/5/31")
                                 select new
                                 {
                                     OrderID = s2.Field<int>("OrderID"),
                                     CustomerName = s1.Field<string>("CompanyName"),
                                     OrderDate = s2.Field<DateTime>("OrderDate")
                                 };

4.Group

  var result = from s in ds.Tables["Orders"].AsEnumerable()
                                 group s by s.Field<string>("CustomerID") into g
                                 select new
                                 {
                                     OrderID = g.First().Field<int>("OrderID"),
                                     CustomerID = g.Key
                                 };

5.返回前10行数据

var table = (from s1 in ds.Tables["Customers"].AsEnumerable() select s1).Take(10);