Beispiele für die methodenbasierte Abfragesyntax: Set-Operatoren (LINQ to DataSet)
In den Beispielen in diesem Thema wird veranschaulicht, wie sie die Operatoren Distinct, Except, Intersectund Union verwenden, um wertbasierte Vergleichsvorgänge für Datasets von Datenzeilen auszuführen. Laden von Daten in ein DataSet finden Sie unter Vergleichen von DataRows weitere Informationen zu DataRowComparer.
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.
Distinct
Beispiel
In diesem Beispiel wird die Distinct-Methode verwendet, um doppelte Elemente in einer Sequenz zu entfernen.
// 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
Except
Beispiel
In diesem Beispiel wird die Except-Methode verwendet, um die Kontakte abzurufen, die zwar in der ersten, nicht aber in der zweiten Tabelle aufgeführt sind.
// 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
Überschneiden
Beispiel
In diesem Beispiel wird die Intersect-Methode verwendet, um die Kontakte abzurufen, die in beiden Tabellen aufgeführt sind.
// 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
Union
Beispiel
In diesem Beispiel wird die Union-Methode verwendet, um die Kontakte abzurufen, die in nur einer der beiden Tabellen aufgeführt sind.
// 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