Linq to DataSet

几点基础：

1： class DataTableExtensions

// DataTable to EnumerableRowCollection<DataRow>, while EnumerableRowCollection<TRow> has implemented
// Ienumerable<T>
public static EnumerableRowCollection<DataRow> AsEnumerable(this DataTable source);

// Ienumerable<T> to datatable

public static DataTable CopyToDataTable<T>(this IEnumerable<T> source) where T : DataRow;

public static void CopyToDataTable<T>(this IEnumerable<T> source, DataTable table, LoadOption options, FillErrorEventHandler errorHandler) where T : DataRow;

 

2: class DataRowExtensions
public static T Field<T>(this DataRow row, string columnName);

public static void SetField<T>(this DataRow row, string columnName, T value);

1：单表查询

var query = products.AsEnumerable().
2    Select(product => new
3    {
4        ProductName = product.Field<string>("Name"),
5        ProductNumber = product.Field<string>("ProductNumber"),
6        Price = product.Field<decimal>("ListPrice")
7    });

2：跨表连接

 1
 2DataTable orders = ds.Tables["SalesOrderHeader"];
 3DataTable details = ds.Tables["SalesOrderDetail"];
 4
 5var query =
 6    from order in orders.AsEnumerable()
 7    join detail in details.AsEnumerable()
 8    on order.Field<int>("SalesOrderID") equals
 9        detail.Field<int>("SalesOrderID")
10    where order.Field<bool>("OnlineOrderFlag") == true
11    && order.Field<DateTime>("OrderDate").Month == 8
12    select new
13    {
14        SalesOrderID =
15            order.Field<int>("SalesOrderID"),
16        SalesOrderDetailID =
17            detail.Field<int>("SalesOrderDetailID"),
18        OrderDate =
19            order.Field<DateTime>("OrderDate"),
20        ProductID =
21            detail.Field<int>("ProductID")
22    };
23