Beispiele für die Abfrageausdruckssyntax: Projektion (LINQ to Entities)
In diesem Thema wird mithilfe von Beispielen gezeigt, wie mithilfe der Select-Methode und der From … From … -Schlüsselwörter das AdventureWorks Sales-Modell unter Verwendung von Abfrageausdruckssyntax abgefragt werden kann. From … From … ist das abfragebasierte Äquivalent der SelectMany-Methode. Für das in den Beispielen verwendete "AdventureWorks Sales"-Modell wurde auf die Tabellen Contact, Address, Product, SalesOrderHeader und SalesOrderDetail der "AdventureWorks"-Beispieldatenbank zurückgegriffen.
Die Beispiele in diesem Thema verwenden die folgenden using/Imports-Anweisungen:
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;
Weitere Informationen finden Sie unter Gewusst wie: Erstellen eines LINQ to Entities-Projekts in Visual Studio.
Select
Beispiel
Im folgenden Beispiel wird die Select-Methode verwendet, um alle Zeilen der Tabelle Product zurückzugeben und die Produktnamen anzuzeigen.
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);
}
}
Beispiel
Im folgenden Beispiel wird Select verwendet, um eine Sequenz zurückzugeben, die nur aus Produktnamen besteht.
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);
}
}
Beispiel
Im folgenden Beispiel wird die Select-Methode verwendet, um die Product.Name-Eigenschaft und die Product.ProductID-Eigenschaft in eine Sequenz anonymer Typen zu projizieren.
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)
Beispiel
Im folgenden Beispiel wird From … From … (das Äquivalent der SelectMany-Methode) verwendet, um alle Aufträge auszuwählen, bei denen TotalDue kleiner als 500,00 ist.
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);
}
}
Beispiel
Im folgenden Beispiel wird From … From … (das Äquivalent der SelectMany-Methode) verwendet, um alle Aufträge auszuwählen, bei denen die Bestellung am 1. Oktober 2002 oder später eingegangen ist.
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);
}
}
Beispiel
Im folgenden Beispiel wird From … From … (das Äquivalent der SelectMany-Methode) verwendet, um alle Aufträge auszuwählen, bei denen die Bestellsumme größer als 10000,00 ist. Mithilfe der From-Zuweisung wird vermieden, dass die Summe zweimal angefordert wird.
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);
}
}