메서드 기반 쿼리 구문 예제: 집합 연산자(LINQ to DataSet)

이 항목의 예는 Distinct, Except, Intersect 및 Union 연산자를 사용하여 데이터 행 집합에 대해 값 기반 비교 작업을 수행하는 방법을 보여 줍니다.DataSet에 데이터 로드DataRowComparer에 대한 자세한 내용은 DataRow 비교를 참조하세요.

이러한 예제에서 사용되는 FillDataSet 메서드는 데이터집합에 데이터를 로드하는데 지정됩니다.

이 항목의 예제에서는 AdventureWorks 샘플 데이터베이스의 Contact, Address, Product, SalesOrderHeader 및 SalesOrderDetail 테이블을 사용합니다.

이 항목의 예제에서는 다음과 같은 using/Imports 문을 사용합니다.

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

자세한 내용은 방법: Visual Studio에서 LINQ to DataSet 프로젝트 만들기를 참조하세요.

Distinct

예시

이 예제에서는 Distinct 메서드를 사용하여 시퀀스의 중복 요소를 제거합니다.

// 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 메서드를 사용하여 첫 번째 테이블에만 있고 두 번째 테이블에는 없는 연락처를 반환합니다.

// 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

Intersect

예시

이 예제에서는 Intersect 메서드를 사용하여 두 테이블 모두에 있는 연락처를 반환합니다.

// 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

예시

이 예제에서는 Union 메서드를 사용하여 두 테이블 중 한 테이블에만 있는 고유 연락처를 반환합니다.

// 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

참고 항목

• 데이터를 데이터 세트에 로드
• LINQ to DataSet 예제
• 표준 쿼리 연산자 개요(C#)
• 표준 쿼리 연산자 개요(Visual Basic)