メソッド ベースのクエリ構文例 : 射影 (LINQ to Entities)
このトピックでは、メソッド ベースのクエリ構文で、Select メソッドおよび SelectMany メソッドを使用して、AdventureWorks Sales Model を照会する例を取り上げます。 The AdventureWorks Sales Model used in these examples is built from the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.
このトピックの例には、次の 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.Name プロパティおよび Product.ProductID プロパティを一連の匿名型に射影します。
Using context As New AdventureWorksEntities
Dim query = context.Products _
.Select(Function(prod) New With _
{ _
.ProductName = prod.Name, _
.ProductId = prod.ProductID _
})
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 = context.Products
.Select(product => 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);
}
}
例
次の例では、Select メソッドを使用して、一連の製品名だけを取得しています。
Using context As New AdventureWorksEntities
Dim productNames = context.Products _
.Select(Function(p) 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 = context.Products
.Select(p => p.Name);
Console.WriteLine("Product Names:");
foreach (String productName in productNames)
{
Console.WriteLine(productName);
}
}
SelectMany
例
次の例では、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 = contacts.SelectMany( _
Function(contact) orders.Where(Function(order) _
(contact.ContactID = order.Contact.ContactID) _
And order.TotalDue < totalDue) _
.Select(Function(order) 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 =
contacts.SelectMany(
contact => orders.Where(order =>
(contact.ContactID == order.Contact.ContactID)
&& order.TotalDue < totalDue)
.Select(order => 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);
}
}
例
次の例では、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 = contacts.SelectMany( _
Function(contact) orders.Where(Function(order) _
(contact.ContactID = order.Contact.ContactID) _
And order.OrderDate >= New DateTime(2002, 10, 1)) _
.Select(Function(order) 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 =
contacts.SelectMany(
contact => orders.Where(order =>
(contact.ContactID == order.Contact.ContactID)
&& order.OrderDate >= new DateTime(2002, 10, 1))
.Select(order => 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);
}
}