This topic has not yet been rated - Rate this topic

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

.NET Framework 4.5

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:

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

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

Distinct

Example

Except

Example

Intersect

Example

Union

Example

See Also

Concepts

Other Resources