Voorbeelden van op methode gebaseerde querysyntaxis: Operatoren instellen (LINQ naar DataSet)
De voorbeelden in dit onderwerp laten zien hoe u de Distinctoperators ExceptIntersectgebruikt om Union vergelijkingsbewerkingen op basis van waarden uit te voeren voor sets met gegevensrijen.Gegevens laden in een DataSet Zie DataRows vergelijken voor meer informatie over DataRowComparer.
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.
Distinct
Opmerking
In dit voorbeeld wordt de Distinct methode gebruikt om dubbele elementen in een reeks te verwijderen.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
List<DataRow> rows = new List<DataRow>();
DataTable contact = ds.Tables["Contact"];
// Get 100 rows from the Contact table.
IEnumerable<DataRow> query = (from c in contact.AsEnumerable()
select c).Take(100);
DataTable contactsTableWith100Rows = query.CopyToDataTable();
// Add 100 rows to the list.
foreach (DataRow row in contactsTableWith100Rows.Rows)
rows.Add(row);
// Create duplicate rows by adding the same 100 rows to the list.
foreach (DataRow row in contactsTableWith100Rows.Rows)
rows.Add(row);
DataTable table =
System.Data.DataTableExtensions.CopyToDataTable<DataRow>(rows);
// Find the unique contacts in the table.
IEnumerable<DataRow> uniqueContacts =
table.AsEnumerable().Distinct(DataRowComparer.Default);
Console.WriteLine("Unique contacts:");
foreach (DataRow uniqueContact in uniqueContacts)
{
Console.WriteLine(uniqueContact.Field<Int32>("ContactID"));
}
' 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 rows As List(Of DataRow) = New List(Of DataRow)
Dim contacts As DataTable = ds.Tables("Contact")
' Get 100 rows from the Contact table.
Dim query = ( _
From c In contacts.AsEnumerable() _
Select c).Take(100)
Dim contactsTableWith100Rows = query.CopyToDataTable()
' Add 100 rows to the list.
For Each row In contactsTableWith100Rows.Rows
rows.Add(row)
Next
' Create duplicate rows by adding the same 100 rows to the list.
For Each row In contactsTableWith100Rows.Rows
rows.Add(row)
Next
Dim table = _
System.Data.DataTableExtensions.CopyToDataTable(Of DataRow)(rows)
' Find the unique contacts in the table.
Dim uniqueContacts = _
table.AsEnumerable().Distinct(DataRowComparer.Default)
Console.WriteLine("Unique contacts:")
For Each uniqueContact In uniqueContacts
Console.WriteLine(uniqueContact.Field(Of Integer)("ContactID"))
Next
Behalve
Opmerking
In dit voorbeeld wordt de Except methode gebruikt om contactpersonen te retourneren die worden weergegeven in de eerste tabel, maar niet in de tweede.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable contactTable = ds.Tables["Contact"];
// Create two tables.
IEnumerable<DataRow> query1 = from contact in contactTable.AsEnumerable()
where contact.Field<string>("Title") == "Ms."
select contact;
IEnumerable<DataRow> query2 = from contact in contactTable.AsEnumerable()
where contact.Field<string>("FirstName") == "Sandra"
select contact;
DataTable contacts1 = query1.CopyToDataTable();
DataTable contacts2 = query2.CopyToDataTable();
// Find the contacts that are in the first
// table but not the second.
var contacts = contacts1.AsEnumerable().Except(contacts2.AsEnumerable(),
DataRowComparer.Default);
Console.WriteLine("Except of employees tables");
foreach (DataRow row in contacts)
{
Console.WriteLine("Id: {0} {1} {2} {3}",
row["ContactID"], row["Title"], row["FirstName"], row["LastName"]);
}
' 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 contactTable As DataTable = ds.Tables("Contact")
Dim query1 = _
From contact In contactTable.AsEnumerable() _
Where contact.Field(Of String)("Title") = "Ms." _
Select contact
Dim query2 = _
From contact In contactTable.AsEnumerable() _
Where contact.Field(Of String)("FirstName") = "Sandra" _
Select contact
Dim contacts1 = query1.CopyToDataTable()
Dim contacts2 = query2.CopyToDataTable()
' Find the contacts that are in the first
' table but not the second.
Dim contacts = contacts1.AsEnumerable().Except(contacts2.AsEnumerable(), _
DataRowComparer.Default)
Console.WriteLine("Except of employees tables")
For Each row In contacts
Console.WriteLine("Id: {0} {1} {2} {3}", _
row("ContactID"), row("Title"), row("FirstName"), row("LastName"))
Next
Snijden
Opmerking
In dit voorbeeld wordt de Intersect methode gebruikt om contactpersonen te retourneren die in beide tabellen worden weergegeven.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable contactTable = ds.Tables["Contact"];
// Create two tables.
IEnumerable<DataRow> query1 = from contact in contactTable.AsEnumerable()
where contact.Field<string>("Title") == "Ms."
select contact;
IEnumerable<DataRow> query2 = from contact in contactTable.AsEnumerable()
where contact.Field<string>("FirstName") == "Sandra"
select contact;
DataTable contacts1 = query1.CopyToDataTable();
DataTable contacts2 = query2.CopyToDataTable();
// Find the intersection of the two tables.
var contacts = contacts1.AsEnumerable().Intersect(contacts2.AsEnumerable(),
DataRowComparer.Default);
Console.WriteLine("Intersection of contacts tables");
foreach (DataRow row in contacts)
{
Console.WriteLine("Id: {0} {1} {2} {3}",
row["ContactID"], row["Title"], row["FirstName"], row["LastName"]);
}
' 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 contactTable As DataTable = ds.Tables("Contact")
Dim query1 = _
From contact In contactTable.AsEnumerable() _
Where contact.Field(Of String)("Title") = "Ms." _
Select contact
Dim query2 = _
From contact In contactTable.AsEnumerable() _
Where contact.Field(Of String)("FirstName") = "Sandra" _
Select contact
Dim contacts1 = query1.CopyToDataTable()
Dim contacts2 = query2.CopyToDataTable()
Dim contacts = contacts1.AsEnumerable() _
.Intersect(contacts2.AsEnumerable(), DataRowComparer.Default)
Console.WriteLine("Intersect of employees tables")
For Each row In contacts
Console.WriteLine("Id: {0} {1} {2} {3}", _
row("ContactID"), row("Title"), row("FirstName"), row("LastName"))
Next
Vakbond
Opmerking
In dit voorbeeld wordt de Union methode gebruikt om unieke contactpersonen uit een van de twee tabellen te retourneren.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
// Create two tables.
DataTable contactTable = ds.Tables["Contact"];
IEnumerable<DataRow> query1 = from contact in contactTable.AsEnumerable()
where contact.Field<string>("Title") == "Ms."
select contact;
IEnumerable<DataRow> query2 = from contact in contactTable.AsEnumerable()
where contact.Field<string>("FirstName") == "Sandra"
select contact;
DataTable contacts1 = query1.CopyToDataTable();
DataTable contacts2 = query2.CopyToDataTable();
// Find the union of the two tables.
var contacts = contacts1.AsEnumerable().Union(contacts2.AsEnumerable(),
DataRowComparer.Default);
Console.WriteLine("Union of contacts tables:");
foreach (DataRow row in contacts)
{
Console.WriteLine("Id: {0} {1} {2} {3}",
row["ContactID"], row["Title"], row["FirstName"], row["LastName"]);
}
' 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 contactTable As DataTable = ds.Tables("Contact")
Dim query1 = _
From contact In contactTable.AsEnumerable() _
Where contact.Field(Of String)("Title") = "Ms." _
Select contact
Dim query2 = _
From contact In contactTable.AsEnumerable() _
Where contact.Field(Of String)("FirstName") = "Sandra" _
Select contact
Dim contacts1 = query1.CopyToDataTable()
Dim contacts2 = query2.CopyToDataTable()
Dim contacts = contacts1.AsEnumerable().Union(contacts2.AsEnumerable(), _
DataRowComparer.Default)
Console.WriteLine("Union of employees tables")
For Each row In contacts
Console.WriteLine("Id: {0} {1} {2} {3}", _
row("ContactID"), row("Title"), row("FirstName"), row("LastName"))
Next