クエリ式の構文例 : 制限 (LINQ to DataSet)

このトピックでは、Where メソッドで、クエリ式の構文を使って DataSet に対するクエリを実行する例を紹介しています。

これらの例で使用されている FillDataSet メソッドの指定については、「DataSet へのデータの読み込み」を参照してください。

このトピックの例には、AdventureWorks サンプル データベースの Contact、Address、Product、SalesOrderHeader、SalesOrderDetail の各テーブルが使用されています。

このトピックの例には、次の using/Imports ステートメントが使用されています。

Option Explicit On

Imports System
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

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Globalization;

詳細については、「方法 : Visual Studio で LINQ to DataSet プロジェクトを作成する」を参照してください。

Where

例

この例では、すべてのオンライン注文が返されます。

' 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

// 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);
}

例

この例では、注文数量が 3 個以上で 5 個以下の注文が返されます。

' 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 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 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

// 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("");
}

例

この例では、Where メソッドを使用して、2002 年 12 月 1 日以降に受けた注文を検索します。次に、GetChildRows メソッドを使用して、各注文の詳細を取得します。

' 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

// 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"]);
    }
}

参照

概念

DataSet へのデータの読み込み

標準クエリ演算子の概要

その他の技術情報

LINQ to DataSet の例