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

  • [アーティクル]

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

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

Option Explicit On
Option Strict On
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 System.Globalization;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Data.Common;

Select

例

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

Using context As New AdventureWorksEntities
    Dim products As ObjectSet(Of Product) = context.Products

    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 context = new AdventureWorksEntities())
{
    IQueryable<Product> productsQuery = from product in context.Products
                                        select product;

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

例

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

Using context As New AdventureWorksEntities
    Dim products As ObjectSet(Of Product) = context.Products

    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 context = new AdventureWorksEntities())
{
    IQueryable<string> productNames =
        from p in context.Products
        select p.Name;

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

例

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

Using context As New AdventureWorksEntities
    Dim products As ObjectSet(Of Product) = context.Products

    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 context = new AdventureWorksEntities())
{
    var query =
        from product in context.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 c From c (SelectMany メソッドと等価) を使用して、TotalDue が 500.00 に満たないすべての注文を選択します。

Dim totalDue = 500D
Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From contact In contacts _
        From order In orders _
        Where contact.ContactID = order.Contact.ContactID _
                And order.TotalDue < totalDue _
        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

decimal totalDue = 500.00M;
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from contact in contacts
        from order in orders
        where contact.ContactID == order.Contact.ContactID
            && order.TotalDue < totalDue
        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 c From c (SelectMany メソッドと等価) を使用して、2002 年 10 月 1 日以降に受けたすべての注文を選択します。

Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    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 context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    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 c From c (SelectMany メソッドと等価) を使用して、注文の合計が 10000.00 を超えるすべての注文を選択します。このとき、From 割り当てを使用して、合計を 2 回要求しないようにしています。

Dim totalDue = 10000D
Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From contact In contacts _
        From order In orders _
        Let total = order.TotalDue _
        Where contact.ContactID = order.Contact.ContactID _
                And total >= totalDue _
        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

decimal totalDue = 10000.0M;
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from contact in contacts
        from order in orders
        let total = order.TotalDue
        where contact.ContactID == order.Contact.ContactID
            && total >= totalDue
        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 でのクエリ