基于方法的查询语法示例：投影
本主题中的示例演示如何使用 Select 和 SelectMany 方法，利用基于方法的查询语法来查询 AdventureWorks 销售模型。 这些示例中使用的 AdventureWorks 销售模型从 AdventureWorks 示例数据库中的 Contact、Address、Product、SalesOrderHeader 和 SalesOrderDetail 等表生成。
本主题中的示例使用下面的 using/Imports 语句：
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;
Option Explicit On
Option Strict On
Imports System.Data.Objects
Imports System.Globalization
Select
示例
以下示例使用 Select 方法以将 Product.Name 和 Product.ProductID 属性投影到一系列匿名类型。
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);
}
}
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
示例
以下示例使用 Select 方法以只返回一系列产品名称。
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);
}
}
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
SelectMany
示例
以下示例使用 SelectMany 方法以选择 TotalDue 低于 500.00 的所有订单。
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);
}
}
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
示例
以下示例使用 SelectMany 方法以选择在 2002 年 10 月 1 或此日期之后发出的所有订单。
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);
}
}
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