Metodbaserade frågesyntaxexempel: Ange operatorer (LINQ till DataSet)
Exemplen i det här avsnittet visar hur du använder Distinctoperatorerna , Except, Intersectoch Union för att utföra värdebaserade jämförelseåtgärder på uppsättningar med datarader.Läsa in data i en datauppsättning Se Jämföra DataRows för mer information om DataRowComparer.
Metoden FillDataSet som används i dessa exempel anges i Läsa in data i en datauppsättning.
Exemplen i det här avsnittet använder tabellerna Kontakt, Adress, Produkt, SalesOrderHeader och SalesOrderDetail i Exempeldatabasen AdventureWorks.
Exemplen i det här avsnittet använder följande using/Imports instruktioner:
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
Mer information finns i How to: Create a LINQ to DataSet Project in Visual Studio (Så här skapar du ett LINQ till datauppsättningsprojekt i Visual Studio).
Distinct
Exempel
I det Distinct här exemplet används metoden för att ta bort duplicerade element i en sekvens.
// 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
Förutom
Exempel
I det Except här exemplet används metoden för att returnera kontakter som visas i den första tabellen men inte i den andra.
// 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
Skär
Exempel
I det Intersect här exemplet används metoden för att returnera kontakter som visas i båda tabellerna.
// 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
Exempel
I det Union här exemplet används metoden för att returnera unika kontakter från någon av de två tabellerna.
// 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