Voorbeelden van op methode gebaseerde querysyntaxis: Projectie (LINQ naar DataSet)
De voorbeelden in dit onderwerp laten zien hoe u de Select en SelectMany methoden gebruikt om een DataSet query uit te voeren met behulp van de op methode gebaseerde querysyntaxis.
De FillDataSet methode die in deze voorbeelden wordt gebruikt, wordt opgegeven in Gegevens laden in een DataSet.
In de voorbeelden in dit onderwerp worden de tabellen Contact, Adres, Product, SalesOrderHeader en SalesOrderDetail gebruikt in de voorbeelddatabase AdventureWorks.
In de voorbeelden in dit onderwerp worden de volgende using/Imports instructies gebruikt:
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
Zie How to: Create a LINQ to DataSet Project in Visual Studio (Een LINQ maken naar DataSet-project in Visual Studio) voor meer informatie.
Select
Opmerking
In dit voorbeeld wordt de Select methode gebruikt om de Name, ProductNumberen ListPrice eigenschappen te projecteren op een reeks anonieme typen. De naam van de ListPrice eigenschap wordt ook gewijzigd Price in het resulterende type.
// 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
Opmerking
In dit voorbeeld wordt de SelectMany methode gebruikt om alle orders te selecteren waarvoor TotalDue minder dan 500,00 is.
// 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
Opmerking
In dit voorbeeld wordt de SelectMany methode gebruikt om alle orders te selecteren waarin de bestelling is gemaakt op 1 oktober 2002 of hoger.
// 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