쿼리 식 구문 예제: 프로젝션(LINQ to DataSet)

아티클
04/07/2023

이 항목의 예제에서는 Select 및 SelectMany 메서드에서 쿼리 식 구문을 사용하여 DataSet을 쿼리하는 방법을 보여 줍니다.

이러한 예제에서 사용되는 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 프로젝트 만들기를 참조하세요.

선택

예시

이 예제에서는 Select 메서드를 사용하여 Product 테이블의 모든 행을 반환하고 제품 이름을 표시합니다.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable products = ds.Tables["Product"];

IEnumerable<DataRow> query =
    from product in products.AsEnumerable()
    select product;

Console.WriteLine("Product Names:");
foreach (DataRow p in query)
{
    Console.WriteLine(p.Field<string>("Name"));
}

' 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 products As DataTable = ds.Tables("Product")

Dim query = From product In products.AsEnumerable() _
            Select product
Console.WriteLine("Product Names:")
For Each p In query
    Console.WriteLine(p.Field(Of String)("Name"))
Next

예시

이 예제에서는 Select를 사용하여 제품 이름만으로 구성된 시퀀스를 반환합니다.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable products = ds.Tables["Product"];

IEnumerable<string> query =
    from product in products.AsEnumerable()
    select product.Field<string>("Name");

Console.WriteLine("Product Names:");
foreach (string productName in query)
{
    Console.WriteLine(productName);
}

' 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 products As DataTable = ds.Tables("Product")

Dim query = From product In products.AsEnumerable() _
            Select product.Field(Of String)("Name")

Console.WriteLine("Product Names:")
For Each productName In query
    Console.WriteLine(productName)
Next

SelectMany

예시

이 예제에서는 From …, …(SelectMany 메서드와 같음)을 사용하여 TotalDue가 500.00보다 작은 모든 주문을 선택합니다.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable contacts = ds.Tables["Contact"];
DataTable orders = ds.Tables["SalesOrderHeader"];

var query =
    from contact in contacts.AsEnumerable()
    from order in orders.AsEnumerable()
    where contact.Field<int>("ContactID") == order.Field<int>("ContactID")
        && order.Field<decimal>("TotalDue") < 500.00M
    select new
    {
        ContactID = contact.Field<int>("ContactID"),
        LastName = contact.Field<string>("LastName"),
        FirstName = contact.Field<string>("FirstName"),
        OrderID = order.Field<int>("SalesOrderID"),
        Total = order.Field<decimal>("TotalDue")
    };

foreach (var smallOrder in query)
{
    Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Total Due: ${4} ",
        smallOrder.ContactID, smallOrder.LastName, smallOrder.FirstName,
        smallOrder.OrderID, smallOrder.Total);
}

' 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 contacts As DataTable = ds.Tables("Contact")
Dim orders As DataTable = ds.Tables("SalesOrderHeader")

Dim query = _
    From contact In contacts.AsEnumerable() _
    From order In orders.AsEnumerable() _
    Where (contact.Field(Of Integer)("ContactID") = _
        order.Field(Of Integer)("ContactID")) _
        And (order.Field(Of Decimal)("TotalDue") < 500D) _
    Select New With _
    { _
        .ContactID = contact.Field(Of Integer)("ContactID"), _
        .LastName = contact.Field(Of String)("LastName"), _
        .FirstName = contact.Field(Of String)("FirstName"), _
        .OrderID = order.Field(Of Integer)("SalesOrderID"), _
        .TotalDue = order.Field(Of Decimal)("TotalDue") _
    }

For Each smallOrder In query
    Console.Write("ContactID: " & smallOrder.ContactID)
    Console.Write(" Name: {0}, {1}", smallOrder.LastName, _
                       smallOrder.FirstName)
    Console.Write(" OrderID: " & smallOrder.OrderID)
    Console.WriteLine(" TotalDue: $" & smallOrder.TotalDue)
Next

예시

이 예제에서는 From …, …(SelectMany 메서드와 같음)을 사용하여 2002년 10월 1일 이후에 작성된 모든 주문을 선택합니다.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable contacts = ds.Tables["Contact"];
DataTable orders = ds.Tables["SalesOrderHeader"];

var query =
    from contact in contacts.AsEnumerable()
    from order in orders.AsEnumerable()
    where contact.Field<int>("ContactID") == order.Field<int>("ContactID") &&
        order.Field<DateTime>("OrderDate") >= new DateTime(2002, 10, 1)
    select new
    {
        ContactID = contact.Field<int>("ContactID"),
        LastName = contact.Field<string>("LastName"),
        FirstName = contact.Field<string>("FirstName"),
        OrderID = order.Field<int>("SalesOrderID"),
        OrderDate = order.Field<DateTime>("OrderDate")
    };

foreach (var order in query)
{
    Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Order date: {4:d} ",
        order.ContactID, order.LastName, order.FirstName,
        order.OrderID, order.OrderDate);
}

' 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 contacts As DataTable = ds.Tables("Contact")
Dim orders As DataTable = ds.Tables("SalesOrderHeader")

Dim query = _
    From contact In contacts.AsEnumerable() _
    From order In orders.AsEnumerable() _
    Where contact.Field(Of Integer)("ContactID") = order.Field(Of Integer)("ContactID") And _
        order.Field(Of DateTime)("OrderDate") >= New DateTime(2002, 10, 1) _
    Select New With _
        { _
           .ContactID = contact.Field(Of Integer)("ContactID"), _
           .LastName = contact.Field(Of String)("LastName"), _
           .FirstName = contact.Field(Of String)("FirstName"), _
           .OrderID = order.Field(Of Integer)("SalesOrderID"), _
           .OrderDate = order.Field(Of DateTime)("OrderDate") _
        }

For Each order In query
    Console.Write("Contact ID: " & order.ContactID)
    Console.Write(" Name: " & order.LastName & ", " & order.FirstName)
    Console.Write(" Order ID: " & order.OrderID)
    Console.WriteLine(" Order date: {0:d} ", order.OrderDate)
Next

예시

이 예제에서는 From …, …(SelectMany 메서드와 같음)을 사용하여 주문 합계가 10000.00보다 큰 모든 주문을 선택하고 From 할당을 사용하여 합계가 두 번 요청되지 않도록 합니다.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable contacts = ds.Tables["Contact"];
DataTable orders = ds.Tables["SalesOrderHeader"];

var query =
    from contact in contacts.AsEnumerable()
    from order in orders.AsEnumerable()
    let total = order.Field<decimal>("TotalDue")
    where contact.Field<int>("ContactID") == order.Field<int>("ContactID") &&
          total >= 10000.0M
    select new
    {
        ContactID = contact.Field<int>("ContactID"),
        LastName = contact.Field<string>("LastName"),
        OrderID = order.Field<int>("SalesOrderID"),
        total
    };
foreach (var order in query)
{
    Console.WriteLine("Contact ID: {0} Last name: {1} Order ID: {2} Total: {3}",
        order.ContactID, order.LastName, order.OrderID, order.total);
}

' 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 contacts As DataTable = ds.Tables("Contact")
Dim orders As DataTable = ds.Tables("SalesOrderHeader")

Dim query = _
    From contact In contacts.AsEnumerable() _
    From order In orders.AsEnumerable() _
    Let total = order.Field(Of Decimal)("TotalDue") _
    Where contact.Field(Of Integer)("ContactID") = order.Field(Of Integer)("ContactID") And _
        total >= 10000D _
    Select New With _
        { _
           .ContactID = contact.Field(Of Integer)("ContactID"), _
           .LastName = contact.Field(Of String)("LastName"), _
           .OrderID = order.Field(Of Integer)("SalesOrderID"), _
           .OrderDate = order.Field(Of DateTime)("OrderDate"), _
           total _
        }

For Each order In query
    Console.Write("Contact ID: " & order.ContactID)
    Console.Write(" Last Name: " & order.LastName)
    Console.Write(" Order ID: " & order.OrderID)
    Console.WriteLine(" Total: $" & order.total)
Next

다음을 통해 공유

쿼리 식 구문 예제: 프로젝션(LINQ to DataSet)

선택

예시

예시

SelectMany

예시

예시

예시

참고 항목

추가 리소스