Method-Based Query Syntax Examples: Set Operators (LINQ to DataSet)

The examples in this topic demonstrate how to use the Distinct, Except, Intersect, and Union operators to perform value-based comparison operations on sets of data rows.Loading Data Into a DataSet See Comparing DataRows (LINQ to DataSet) for more information on DataRowComparer.

The FillDataSet method used in these examples is specified in Loading Data Into a DataSet.

The examples in this topic use the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.

The examples in this topic use the following using/Imports statements:

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;

For more information, see How to: Create a LINQ to DataSet Project In Visual Studio.

Distinct

Example

This example uses the Distinct method to remove duplicate elements in a sequence.

' 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

Example

This example uses the Except method to return contacts that appear in the first table but not in the second.

' 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

Example

This example uses the Intersect method to return contacts that appear in both 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

Example

This example uses the Union method to return unique contacts from either of the two 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"]);
}

See Also

Concepts

Loading Data Into a DataSet

Standard Query Operators Overview

Other Resources

LINQ to DataSet Examples