Delen via

Voorbeelden van syntaxis van queryexpressie: projectie (LINQ naar dataset)

De voorbeelden in dit onderwerp laten zien hoe u de methoden Select en SelectMany gebruikt om een query uit te voeren op een DataSet met behulp van de syntaxis van de query-expressie.

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 verklaringen 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

Voor meer informatie, zie Hoe te: Een LINQ naar DataSet-project maken in Visual Studio:.

Selecteren

Voorbeeld

In dit voorbeeld wordt de methode Select gebruikt om alle rijen uit de Product tabel te retourneren en de productnamen weer te geven.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable products = ds.Tables["Product"];

IEnumerable<DataRow> query =
    from product in products.AsEnumerable()
    select product;

Console.WriteLine("Product Names:");
foreach (DataRow p in query)
{
    Console.WriteLine(p.Field<string>("Name"));
}

' 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 = From product In products.AsEnumerable() _
            Select product
Console.WriteLine("Product Names:")
For Each p In query
    Console.WriteLine(p.Field(Of String)("Name"))
Next

Voorbeeld

In dit voorbeeld wordt Select gebruikt om een reeks alleen productnamen te retourneren.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable products = ds.Tables["Product"];

IEnumerable<string> query =
    from product in products.AsEnumerable()
    select product.Field<string>("Name");

Console.WriteLine("Product Names:");
foreach (string productName in query)
{
    Console.WriteLine(productName);
}

' 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 = From product In products.AsEnumerable() _
            Select product.Field(Of String)("Name")

Console.WriteLine("Product Names:")
For Each productName In query
    Console.WriteLine(productName)
Next

SelectMany

Voorbeeld

In dit voorbeeld wordt From …, … (het equivalent van de methode SelectMany) gebruikt om alle orders te selecteren waarbij TotalDue kleiner is dan 500,00.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable contacts = ds.Tables["Contact"];
DataTable orders = ds.Tables["SalesOrderHeader"];

var query =
    from contact in contacts.AsEnumerable()
    from order in orders.AsEnumerable()
    where contact.Field<int>("ContactID") == order.Field<int>("ContactID")
        && order.Field<decimal>("TotalDue") < 500.00M
    select 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 As DataTable = ds.Tables("Contact")
Dim orders As DataTable = ds.Tables("SalesOrderHeader")

Dim query = _
    From contact In contacts.AsEnumerable() _
    From order In orders.AsEnumerable() _
    Where (contact.Field(Of Integer)("ContactID") = _
        order.Field(Of Integer)("ContactID")) _
        And (order.Field(Of Decimal)("TotalDue") < 500D) _
    Select 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"), _
        .TotalDue = order.Field(Of Decimal)("TotalDue") _
    }

For Each smallOrder In query
    Console.Write("ContactID: " & smallOrder.ContactID)
    Console.Write(" Name: {0}, {1}", smallOrder.LastName, _
                       smallOrder.FirstName)
    Console.Write(" OrderID: " & smallOrder.OrderID)
    Console.WriteLine(" TotalDue: $" & smallOrder.TotalDue)
Next

Voorbeeld

In dit voorbeeld wordt From …, … (het equivalent van de methode SelectMany) 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);

DataTable contacts = ds.Tables["Contact"];
DataTable orders = ds.Tables["SalesOrderHeader"];

var query =
    from contact in contacts.AsEnumerable()
    from order in orders.AsEnumerable()
    where contact.Field<int>("ContactID") == order.Field<int>("ContactID") &&
        order.Field<DateTime>("OrderDate") >= new DateTime(2002, 10, 1)
    select 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 As DataTable = ds.Tables("Contact")
Dim orders As DataTable = ds.Tables("SalesOrderHeader")

Dim query = _
    From contact In contacts.AsEnumerable() _
    From order In orders.AsEnumerable() _
    Where contact.Field(Of Integer)("ContactID") = order.Field(Of Integer)("ContactID") And _
        order.Field(Of DateTime)("OrderDate") >= New DateTime(2002, 10, 1) _
    Select 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

Voorbeeld

In dit voorbeeld wordt een From …, … (het equivalent van de methode SelectMany) gebruikt om alle orders te selecteren waarbij het ordertotaal groter is dan 10000,00 en From toewijzing gebruikt om te voorkomen dat het totaal twee keer wordt aangevraagd.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable contacts = ds.Tables["Contact"];
DataTable orders = ds.Tables["SalesOrderHeader"];

var query =
    from contact in contacts.AsEnumerable()
    from order in orders.AsEnumerable()
    let total = order.Field<decimal>("TotalDue")
    where contact.Field<int>("ContactID") == order.Field<int>("ContactID") &&
          total >= 10000.0M
    select new
    {
        ContactID = contact.Field<int>("ContactID"),
        LastName = contact.Field<string>("LastName"),
        OrderID = order.Field<int>("SalesOrderID"),
        total
    };
foreach (var order in query)
{
    Console.WriteLine($"Contact ID: {order.ContactID} Last name: {order.LastName} Order ID: {order.OrderID} Total: {order.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 As DataTable = ds.Tables("Contact")
Dim orders As DataTable = ds.Tables("SalesOrderHeader")

Dim query = _
    From contact In contacts.AsEnumerable() _
    From order In orders.AsEnumerable() _
    Let total = order.Field(Of Decimal)("TotalDue") _
    Where contact.Field(Of Integer)("ContactID") = order.Field(Of Integer)("ContactID") And _
        total >= 10000D _
    Select New With _
        { _
           .ContactID = contact.Field(Of Integer)("ContactID"), _
           .LastName = contact.Field(Of String)("LastName"), _
           .OrderID = order.Field(Of Integer)("SalesOrderID"), _
           .OrderDate = order.Field(Of DateTime)("OrderDate"), _
           total _
        }

For Each order In query
    Console.Write("Contact ID: " & order.ContactID)
    Console.Write(" Last Name: " & order.LastName)
    Console.Write(" Order ID: " & order.OrderID)
    Console.WriteLine(" Total: $" & order.total)
Next

Zie ook

  • Last updated on