以方法為基礎的查詢語法範例：投影 (LINQ to DataSet)
此主題中的範例將示範如何使用 Select 和 SelectMany 方法並搭配以方法為基礎的查詢語法來查詢 DataSet。
這些範例使用的 FillDataSet 方法，會在將資料載入資料集時指定。
此主題中的範例將使用 AdventureWorks 範例資料庫中的 Contact、Address、Product、SalesOrderHeader 和 SalesOrderDetail 資料表。
此主題中的範例使用下列 using/Imports 陳述式：
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Windows.Forms;
Option Explicit On
Imports System.Linq
Imports System.Linq.Expressions
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Globalization
如需詳細資訊，請參閱操作說明：在 Visual Studio 中建立 LINQ to DataSet 專案。
選取
範例
這則範例會使用 Select 方法，將 Name、ProductNumber 和 ListPrice 屬性規劃成匿名型別的序列 (Sequence)。 ListPrice 屬性也會重新命名為結果型別中的 Price。
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
var query = products.AsEnumerable().
Select(product => new
{
ProductName = product.Field<string>("Name"),
ProductNumber = product.Field<string>("ProductNumber"),
Price = product.Field<decimal>("ListPrice")
});
Console.WriteLine("Product Info:");
foreach (var productInfo in query)
{
Console.WriteLine("Product name: {0} Product number: {1} List price: ${2} ",
productInfo.ProductName, productInfo.ProductNumber, productInfo.Price);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim products As DataTable = ds.Tables("Product")
Dim query = products.AsEnumerable() _
.Select(Function(product As DataRow) New With _
{ _
.ProductName = product.Field(Of String)("Name"), _
.ProductNumber = product.Field(Of String)("ProductNumber"), _
.Price = product.Field(Of Decimal)("ListPrice") _
})
Console.WriteLine("Product Info:")
For Each product In query
Console.Write("Product name: " & product.ProductName)
Console.Write("Product number: " & product.ProductNumber)
Console.WriteLine("List price: $ " & product.Price)
Next
SelectMany
範例
這則範例會使用 SelectMany 方法來選取 TotalDue 少於 500.00 的所有訂單。
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
var contacts = ds.Tables["Contact"].AsEnumerable();
var orders = ds.Tables["SalesOrderHeader"].AsEnumerable();
var query =
contacts.SelectMany(
contact => orders.Where(order =>
(contact.Field<Int32>("ContactID") == order.Field<Int32>("ContactID"))
&& order.Field<decimal>("TotalDue") < 500.00M)
.Select(order => new
{
ContactID = contact.Field<int>("ContactID"),
LastName = contact.Field<string>("LastName"),
FirstName = contact.Field<string>("FirstName"),
OrderID = order.Field<int>("SalesOrderID"),
Total = order.Field<decimal>("TotalDue")
}));
foreach (var smallOrder in query)
{
Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Total Due: ${4} ",
smallOrder.ContactID, smallOrder.LastName, smallOrder.FirstName,
smallOrder.OrderID, smallOrder.Total);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim contacts = ds.Tables("Contact").AsEnumerable()
Dim orders = ds.Tables("SalesOrderHeader").AsEnumerable()
Dim query = _
contacts.SelectMany( _
Function(contact) orders.Where(Function(order) _
(contact.Field(Of Int32)("ContactID") = order.Field(Of Int32)("ContactID")) _
And order.Field(Of Decimal)("TotalDue") < 500D) _
.Select(Function(order) New With _
{ _
.ContactID = contact.Field(Of Integer)("ContactID"), _
.LastName = contact.Field(Of String)("LastName"), _
.FirstName = contact.Field(Of String)("FirstName"), _
.OrderID = order.Field(Of Integer)("SalesOrderID"), _
.Total = order.Field(Of Decimal)("TotalDue") _
}))
For Each smallOrder In query
Console.Write("ContactID: " & smallOrder.ContactID)
Console.Write(" Name: " & smallOrder.LastName & ", " & smallOrder.FirstName)
Console.Write(" Order ID: " & smallOrder.OrderID)
Console.WriteLine(" Total Due: $" & smallOrder.Total)
Next
範例
這則範例會使用 SelectMany 方法來選取在 2002 年 10 月 1 日或之後下單的所有訂單。
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
var contacts = ds.Tables["Contact"].AsEnumerable();
var orders = ds.Tables["SalesOrderHeader"].AsEnumerable();
var query =
contacts.SelectMany(
contact => orders.Where(order =>
(contact.Field<Int32>("ContactID") == order.Field<Int32>("ContactID"))
&& order.Field<DateTime>("OrderDate") >= new DateTime(2002, 10, 1))
.Select(order => new
{
ContactID = contact.Field<int>("ContactID"),
LastName = contact.Field<string>("LastName"),
FirstName = contact.Field<string>("FirstName"),
OrderID = order.Field<int>("SalesOrderID"),
OrderDate = order.Field<DateTime>("OrderDate")
}));
foreach (var order in query)
{
Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Order date: {4:d} ",
order.ContactID, order.LastName, order.FirstName,
order.OrderID, order.OrderDate);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim contacts = ds.Tables("Contact").AsEnumerable()
Dim orders = ds.Tables("SalesOrderHeader").AsEnumerable()
Dim query = _
contacts.SelectMany( _
Function(contact) orders.Where(Function(order) _
(contact.Field(Of Int32)("ContactID") = order.Field(Of Int32)("ContactID")) _
And order.Field(Of DateTime)("OrderDate") >= New DateTime(2002, 10, 1)) _
.Select(Function(order) New With _
{ _
.ContactID = contact.Field(Of Integer)("ContactID"), _
.LastName = contact.Field(Of String)("LastName"), _
.FirstName = contact.Field(Of String)("FirstName"), _
.OrderID = order.Field(Of Integer)("SalesOrderID"), _
.OrderDate = order.Field(Of DateTime)("OrderDate") _
}))
For Each order In query
Console.Write("Contact ID: " & order.ContactID)
Console.Write(" Name: " & order.LastName & ", " & order.FirstName)
Console.Write(" Order ID: " & order.OrderID)
Console.WriteLine(" Order date: {0:d}", order.OrderDate)
Next