Exemples de syntaxe de requête fondée sur une méthode : opérateurs de jeu (LINQ to DataSet)

Les exemples de cette rubrique montrent comment utiliser les opérateurs Distinct, Except, Intersect et Union pour effectuer des opérations de comparaison basée sur les valeurs sur des ensembles de lignes de données.Chargement de données dans un DataSet Voir Comparaison de DataRows (LINQ à DataSet) pour plus d'informations sur DataRowComparer.

La méthode FillDataSet utilisée dans ces exemples est spécifiée dans Chargement de données dans un DataSet.

Les exemples de cette rubrique utilisent les tables Contact, Address, Product, SalesOrderHeader et SalesOrderDetail de l'exemple de base de données AdventureWorks.

Les exemples de cette rubrique utilisent les instructions using/Imports suivantes :

Option Explicit On

Imports System
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

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Globalization;

Pour plus d'informations, consultez Procédure : créer un projet LINQ to DataSet dans Visual Studio.

Distinct

Exemple

Cet exemple utilise la méthode Distinct pour supprimer des éléments en double dans une séquence.

' 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

// 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"));
}

Except

Exemple

Cet exemple utilise la méthode Except pour retourner les contacts qui apparaissent dans la première table, mais pas dans la seconde.

' 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

// 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"]);
}

Intersect

Exemple

Cet exemple utilise la méthode Intersect pour retourner les contacts qui apparaissent dans les deux tables.

' 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

// 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"]);
}

Union

Exemple

Cet exemple utilise la méthode Union pour retourner des contacts uniques dans l'une ou l'autre des tables.

' 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

// 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"]);
}

Voir aussi

Concepts

Chargement de données dans un DataSet

Vue d'ensemble des opérateurs de requête standard

Autres ressources

Exemples de LINQ to DataSet