Beispiele für die methodenbasierte Abfragesyntax: Projektion (LINQ to DataSet)
In den Beispielen in diesem Thema wird gezeigt, wie Sie mithilfe der Methoden Select und SelectMany und der methodenbasierten Abfragesyntax ein DataSet abfragen können.
Die in diesen Beispielen verwendete FillDataSet-Methode wird unter Laden von Daten in ein DataSet beschrieben.
In den Beispielen in diesem Thema wird auf die Tabellen <legacyBold>Contact</legacyBold>, <legacyBold>Address</legacyBold>, <legacyBold>Product</legacyBold>, <legacyBold>SalesOrderHeader</legacyBold> und <legacyBold>SalesOrderDetail</legacyBold> in der <legacyBold>AdventureWorks</legacyBold>-Beispieldatenbank zurückgegriffen.
Die Beispiele in diesem Thema beziehen sich auf die folgenden using/Imports-Anweisungen:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Windows.Forms;
Option Explicit On
Imports System.Linq
Imports System.Linq.Expressions
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Globalization
Weitere Informationen finden Sie unter Erstellen eines LINQ to DataSet-Projekts in Visual Studio.
Select
Beispiel
In diesem Beispiel wird die Select-Methode verwendet, um die Eigenschaften Name, ProductNumber und ListPrice auf eine Abfolge anonymer Typen zu projizieren. Im sich dabei ergebenden Typ wird außerdem die ListPrice-Eigenschaft in Price umbenannt.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
var query = products.AsEnumerable().
Select(product => new
{
ProductName = product.Field<string>("Name"),
ProductNumber = product.Field<string>("ProductNumber"),
Price = product.Field<decimal>("ListPrice")
});
Console.WriteLine("Product Info:");
foreach (var productInfo in query)
{
Console.WriteLine("Product name: {0} Product number: {1} List price: ${2} ",
productInfo.ProductName, productInfo.ProductNumber, productInfo.Price);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim products As DataTable = ds.Tables("Product")
Dim query = products.AsEnumerable() _
.Select(Function(product As DataRow) New With _
{ _
.ProductName = product.Field(Of String)("Name"), _
.ProductNumber = product.Field(Of String)("ProductNumber"), _
.Price = product.Field(Of Decimal)("ListPrice") _
})
Console.WriteLine("Product Info:")
For Each product In query
Console.Write("Product name: " & product.ProductName)
Console.Write("Product number: " & product.ProductNumber)
Console.WriteLine("List price: $ " & product.Price)
Next
SelectMany
Beispiel
In diesem Beispiel wird die SelectMany-Methode verwendet, um alle Aufträge auszuwählen, bei denen TotalDue kleiner als 500.00 ist.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
var contacts = ds.Tables["Contact"].AsEnumerable();
var orders = ds.Tables["SalesOrderHeader"].AsEnumerable();
var query =
contacts.SelectMany(
contact => orders.Where(order =>
(contact.Field<Int32>("ContactID") == order.Field<Int32>("ContactID"))
&& order.Field<decimal>("TotalDue") < 500.00M)
.Select(order => new
{
ContactID = contact.Field<int>("ContactID"),
LastName = contact.Field<string>("LastName"),
FirstName = contact.Field<string>("FirstName"),
OrderID = order.Field<int>("SalesOrderID"),
Total = order.Field<decimal>("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);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim contacts = ds.Tables("Contact").AsEnumerable()
Dim orders = ds.Tables("SalesOrderHeader").AsEnumerable()
Dim query = _
contacts.SelectMany( _
Function(contact) orders.Where(Function(order) _
(contact.Field(Of Int32)("ContactID") = order.Field(Of Int32)("ContactID")) _
And order.Field(Of Decimal)("TotalDue") < 500D) _
.Select(Function(order) New With _
{ _
.ContactID = contact.Field(Of Integer)("ContactID"), _
.LastName = contact.Field(Of String)("LastName"), _
.FirstName = contact.Field(Of String)("FirstName"), _
.OrderID = order.Field(Of Integer)("SalesOrderID"), _
.Total = order.Field(Of Decimal)("TotalDue") _
}))
For Each smallOrder In query
Console.Write("ContactID: " & smallOrder.ContactID)
Console.Write(" Name: " & smallOrder.LastName & ", " & smallOrder.FirstName)
Console.Write(" Order ID: " & smallOrder.OrderID)
Console.WriteLine(" Total Due: $" & smallOrder.Total)
Next
Beispiel
In diesem Beispiel wird die SelectMany-Methode verwendet, um alle Aufträge auszuwählen, bei denen die Bestellung am 1. Oktober 2002 oder später eingegangen ist.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
var contacts = ds.Tables["Contact"].AsEnumerable();
var orders = ds.Tables["SalesOrderHeader"].AsEnumerable();
var query =
contacts.SelectMany(
contact => orders.Where(order =>
(contact.Field<Int32>("ContactID") == order.Field<Int32>("ContactID"))
&& order.Field<DateTime>("OrderDate") >= new DateTime(2002, 10, 1))
.Select(order => new
{
ContactID = contact.Field<int>("ContactID"),
LastName = contact.Field<string>("LastName"),
FirstName = contact.Field<string>("FirstName"),
OrderID = order.Field<int>("SalesOrderID"),
OrderDate = order.Field<DateTime>("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);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim contacts = ds.Tables("Contact").AsEnumerable()
Dim orders = ds.Tables("SalesOrderHeader").AsEnumerable()
Dim query = _
contacts.SelectMany( _
Function(contact) orders.Where(Function(order) _
(contact.Field(Of Int32)("ContactID") = order.Field(Of Int32)("ContactID")) _
And order.Field(Of DateTime)("OrderDate") >= New DateTime(2002, 10, 1)) _
.Select(Function(order) New With _
{ _
.ContactID = contact.Field(Of Integer)("ContactID"), _
.LastName = contact.Field(Of String)("LastName"), _
.FirstName = contact.Field(Of String)("FirstName"), _
.OrderID = order.Field(Of Integer)("SalesOrderID"), _
.OrderDate = order.Field(Of DateTime)("OrderDate") _
}))
For Each order In query
Console.Write("Contact ID: " & order.ContactID)
Console.Write(" Name: " & order.LastName & ", " & order.FirstName)
Console.Write(" Order ID: " & order.OrderID)
Console.WriteLine(" Order date: {0:d}", order.OrderDate)
Next