クエリ式の構文例:制限 (LINQ to DataSet)
このトピックでは、Where メソッドで、クエリ式の構文を使って DataSet に対するクエリを実行する例を紹介しています。
これらの例で使用されている FillDataSet メソッドの指定については、「DataSet へのデータの読み込み」を参照してください。
このトピックの例には、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;
詳細については、Visual Studio で LINQ to DataSet プロジェクトを作成する」を参照してください。
Where
例
この例では、すべてのオンライン注文が返されます。
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
where order.Field<bool>("OnlineOrderFlag") == true
select new
{
SalesOrderID = order.Field<int>("SalesOrderID"),
OrderDate = order.Field<DateTime>("OrderDate"),
SalesOrderNumber = order.Field<string>("SalesOrderNumber")
};
foreach (var onlineOrder in query)
{
Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}",
onlineOrder.SalesOrderID,
onlineOrder.OrderDate,
onlineOrder.SalesOrderNumber);
}
' 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 orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Where order.Field(Of Boolean)("OnlineOrderFlag") = True _
Select New With { _
.SalesOrderID = order.Field(Of Integer)("SalesOrderID"), _
.OrderDate = order.Field(Of DateTime)("OrderDate"), _
.SalesOrderNumber = order.Field(Of String)("SalesOrderNumber") _
}
For Each onlineOrder In query
Console.Write("Order ID: " & onlineOrder.SalesOrderID)
Console.Write(" Order date: " & onlineOrder.OrderDate)
Console.WriteLine(" Order number: " & onlineOrder.SalesOrderNumber)
Next
例
この例では、注文数量が 3 個以上で 5 個以下の注文が返されます。
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderDetail"];
var query =
from order in orders.AsEnumerable()
where order.Field<Int16>("OrderQty") > 2 &&
order.Field<Int16>("OrderQty") < 6
select new
{
SalesOrderID = (int)order.Field<int>("SalesOrderID"),
OrderQty = order.Field<Int16>("OrderQty")
};
foreach (var order in query)
{
Console.WriteLine("Order ID: {0} Order quantity: {1}",
order.SalesOrderID, order.OrderQty);
}
' 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 orders As DataTable = ds.Tables("SalesOrderDetail")
Dim query = _
From order In orders.AsEnumerable() _
Where order.Field(Of Short)("OrderQty") > 2 And _
order.Field(Of Short)("OrderQty") < 6 _
Select New With _
{ _
.SalesOrderID = order.Field(Of Integer)("SalesOrderID"), _
.OrderQty = order.Field(Of Short)("OrderQty") _
}
For Each order In query
Console.Write("Order ID: " & order.SalesOrderID)
Console.WriteLine(" Order quantity: " & order.OrderQty)
Next
例
この例では、色が赤の製品がすべて返されます。
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
var query =
from product in products.AsEnumerable()
where product.Field<string>("Color") == "Red"
select new
{
Name = product.Field<string>("Name"),
ProductNumber = product.Field<string>("ProductNumber"),
ListPrice = product.Field<Decimal>("ListPrice")
};
foreach (var product in query)
{
Console.WriteLine("Name: {0}", product.Name);
Console.WriteLine("Product number: {0}", product.ProductNumber);
Console.WriteLine("List price: ${0}", product.ListPrice);
Console.WriteLine("");
}
' 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 = _
From product In products.AsEnumerable() _
Where product.Field(Of String)("Color") = "Red" _
Select New With _
{ _
.Name = product.Field(Of String)("Name"), _
.ProductNumber = product.Field(Of String)("ProductNumber"), _
.ListPrice = product.Field(Of Decimal)("ListPrice") _
}
For Each product In query
Console.WriteLine("Name: " & product.Name)
Console.WriteLine("Product number: " & product.ProductNumber)
Console.WriteLine("List price: $ " & product.ListPrice & vbNewLine)
Next
例
この例では、Where メソッドを使用して、2002 年 12 月 1 日以降に受けた注文を検索します。次に、GetChildRows メソッドを使用して、各注文の詳細を取得します。
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
IEnumerable<DataRow> query =
from order in orders.AsEnumerable()
where order.Field<DateTime>("OrderDate") >= new DateTime(2002, 12, 1)
select order;
Console.WriteLine("Orders that were made after 12/1/2002:");
foreach (DataRow order in query)
{
Console.WriteLine("OrderID {0} Order date: {1:d} ",
order.Field<int>("SalesOrderID"), order.Field<DateTime>("OrderDate"));
foreach (DataRow orderDetail in order.GetChildRows("SalesOrderHeaderDetail"))
{
Console.WriteLine(" Product ID: {0} Unit Price {1}",
orderDetail["ProductID"], orderDetail["UnitPrice"]);
}
}
' 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 orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query As IEnumerable(Of DataRow) = _
From order In orders.AsEnumerable() _
Where order.Field(Of DateTime)("OrderDate") >= New DateTime(2002, 12, 1) _
Select order
Console.WriteLine("Orders that were made after 12/1/2002:")
For Each order As DataRow In query
Console.WriteLine("OrderID {0} Order date: {1:d} ", _
order.Field(Of Integer)("SalesOrderID"), order.Field(Of DateTime)("OrderDate"))
For Each orderDetail As DataRow In order.GetChildRows("SalesOrderHeaderDetail")
Console.WriteLine(" Product ID: {0} Unit Price {1}", _
orderDetail("ProductID"), orderDetail("UnitPrice"))
Next
Next