クエリ式の構文例 : 射影 (LINQ to Entities)

このトピックでは、クエリ式の構文で、Select メソッドおよび From … From … キーワードを使用して、AdventureWorks Sales Model を照会する例を取り上げます。From … From … は、SelectMany メソッドと等価なクエリ構文です。これらの例で使用されている、AdventureWorks Sales Model は、AdventureWorks サンプル データベースの Contact、Address、Product、SalesOrderHeader、SalesOrderDetail の各テーブルから作成されています。

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

Option Explicit On
Option Strict On
Imports L2EExamplesVB.AdventureWorksModel
Imports System.Data.Objects
Imports System.Globalization

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using AdventureWorksModel;
using System.Globalization;

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

Select

例

次の例では、Select メソッドを使用して Product テーブルからすべての行を取得し、製品名を表示しています。

Using AWEntities As New AdventureWorksEntities
    Dim products As ObjectQuery(Of Product) = AWEntities.Product

    Dim productsQuery = _
        From product In products _
        Select product

    Console.WriteLine("Product Names:")
    For Each product In productsQuery
        Console.WriteLine(product.Name)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Product> products = AWEntities.Product;
    IQueryable<Product> productsQuery = from product in products
                                        select product;

    Console.WriteLine("Product Names:");
    foreach (var prod in productsQuery)
    {
        Console.WriteLine(prod.Name);
    }
}

例

次の例では、Select を使用して、一連の製品名だけを取得しています。

Using AWEntities As New AdventureWorksEntities
    Dim products As ObjectQuery(Of Product) = AWEntities.Product

    Dim productNames = _
        From p In products _
        Select p.Name

    Console.WriteLine("Product Names:")
    For Each productName In productNames
        Console.WriteLine(productName)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Product> products = AWEntities.Product;

    IQueryable<string> productNames =
        from p in products
        select p.Name;

    Console.WriteLine("Product Names:");
    foreach (String productName in productNames)
    {
        Console.WriteLine(productName);
    }
}

例

次の例では、Select メソッドを使用して、Product.Name プロパティおよび Product.ProductID プロパティを一連の匿名型に投影します。

Using AWEntities As New AdventureWorksEntities
    Dim products As ObjectQuery(Of Product) = AWEntities.Product

    Dim query = _
        From product In products _
        Select New With _
        { _
            .ProductId = product.ProductID, _
            .ProductName = product.Name _
        }

    Console.WriteLine("Product Info:")
    For Each productInfo In query
        Console.WriteLine("Product Id: {0} Product name: {1} ", _
                productInfo.ProductId, productInfo.ProductName)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Product> products = AWEntities.Product;
    var query =
        from product in products
        select new
        {
            ProductId = product.ProductID,
            ProductName = product.Name
        };

    Console.WriteLine("Product Info:");
    foreach (var productInfo in query)
    {
        Console.WriteLine("Product Id: {0} Product name: {1} ",
            productInfo.ProductId, productInfo.ProductName);
    }
}

From … From … (SelectMany)

例

次の例では、From … From … (SelectMany メソッドと等価) を使用して、TotalDue が 500.00 に満たないすべての注文を選択します。

Using AWEntities As New AdventureWorksEntities
    Dim contacts As ObjectQuery(Of Contact) = AWEntities.Contact
    Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader

    Dim query = _
        From contact In contacts _
        From order In orders _
        Where contact.ContactID = order.Contact.ContactID _
                And order.TotalDue < 500D _
        Select New With _
        { _
            .ContactID = contact.ContactID, _
            .LastName = contact.LastName, _
            .FirstName = contact.FirstName, _
            .OrderID = order.SalesOrderID, _
            .Total = order.TotalDue _
        }

    For Each 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)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Contact> contacts = AWEntities.Contact;
    ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;

    var query =
        from contact in contacts
        from order in orders
        where contact.ContactID == order.Contact.ContactID
            && order.TotalDue < 500.00M
        select new
        {
            ContactID = contact.ContactID,
            LastName = contact.LastName,
            FirstName = contact.FirstName,
            OrderID = order.SalesOrderID,
            Total = order.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);
    }
}

例

次の例では、From … From … (SelectMany メソッドと等価) を使用して、2002 年 10 月 1 日以降に受けたすべての注文を選択します。

Using AWEntities As New AdventureWorksEntities
    Dim contacts As ObjectQuery(Of Contact) = AWEntities.Contact
    Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader

    Dim query = _
        From contact In contacts _
        From order In orders _
        Where contact.ContactID = order.Contact.ContactID _
                And order.OrderDate >= New DateTime(2002, 10, 1) _
        Select New With _
        { _
            .ContactID = contact.ContactID, _
            .LastName = contact.LastName, _
            .FirstName = contact.FirstName, _
            .OrderID = order.SalesOrderID, _
            .OrderDate = order.OrderDate _
        }

    For Each 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)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Contact> contacts = AWEntities.Contact;
    ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;

    var query =
        from contact in contacts
        from order in orders
        where contact.ContactID == order.Contact.ContactID
            && order.OrderDate >= new DateTime(2002, 10, 1)
        select new
        {
            ContactID = contact.ContactID,
            LastName = contact.LastName,
            FirstName = contact.FirstName,
            OrderID = order.SalesOrderID,
            OrderDate = order.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);
    }
}

例

次の例では、From … From … (SelectMany メソッドと等価) を使用して、注文の合計が 10000.00 を超えるすべての注文を選択します。このとき、From 割り当てを使用して、合計を 2 回要求しないようにしています。

Using AWEntities As New AdventureWorksEntities
    Dim contacts As ObjectQuery(Of Contact) = AWEntities.Contact
    Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader

    Dim query = _
        From contact In contacts _
        From order In orders _
        Let total = order.TotalDue _
        Where contact.ContactID = order.Contact.ContactID _
                And total >= 10000D _
        Select New With _
        { _
            .ContactID = contact.ContactID, _
            .LastName = contact.LastName, _
            .OrderID = order.SalesOrderID, _
            total _
        }

    For Each order In query
        Console.WriteLine("Contact ID: {0} Last name: {1} Order ID: {2} Total: {3}", _
                order.ContactID, order.LastName, order.OrderID, order.total)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Contact> contacts = AWEntities.Contact;
    ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;

    var query =
        from contact in contacts
        from order in orders
        let total = order.TotalDue
        where contact.ContactID == order.Contact.ContactID
            && total >= 10000.0M
        select new
        {
            ContactID = contact.ContactID,
            LastName = contact.LastName,
            OrderID = order.SalesOrderID,
            total
        };

    foreach (var order in query)
    {
        Console.WriteLine("Contact ID: {0} Last name: {1} Order ID: {2} Total: {3}",
            order.ContactID, order.LastName, order.OrderID, order.total);
    }
}

参照

概念

クエリ式の構文例 (LINQ to Entities)