쿼리 식 구문 예제: 그룹화(LINQ to Entities)

이 항목의 예제에서는 쿼리 식 구문을 사용하여 AdventureWorks Sales 모델을 쿼리하기 위해 GroupBy 메서드를 사용하는 방법을 보여 줍니다. 이 예제에서 사용하는 AdventureWorks Sales 모델은 AdventureWorks 샘플 데이터베이스의 Contact, Address, Product, SalesOrderHeader 및 SalesOrderDetail 테이블로 작성되었습니다.

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

Option Explicit On
Option Strict On
Imports System.Data.Objects
Imports System.Globalization

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Globalization;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Data.Common;

예제

다음 예제에서는 우편 번호로 그룹화된 Address 개체를 반환합니다. 결과는 익명 형식으로 프로젝션됩니다.

Using context As New AdventureWorksEntities
    Dim addresses As ObjectSet(Of Address) = context.Addresses

    Dim query = _
        From adrs In addresses _
        Group adrs By adrs.PostalCode Into g = Group _
        Select New With {.PostalCode = PostalCode, .AddressLine = g}

    For Each addressGroup In query
        Console.WriteLine("Postal Code: {0}", addressGroup.PostalCode)
        For Each adrs In addressGroup.AddressLine
            Console.WriteLine(vbTab & adrs.AddressLine1 & _
                adrs.AddressLine2)
        Next
    Next
End Using

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query =
        from address in context.Addresses
        group address by address.PostalCode into addressGroup
        select new { PostalCode = addressGroup.Key, 
                     AddressLine = addressGroup };

    foreach (var addressGroup in query)
    {
        Console.WriteLine("Postal Code: {0}", addressGroup.PostalCode);
        foreach (var address in addressGroup.AddressLine)
        {
            Console.WriteLine("\t" + address.AddressLine1 +
                address.AddressLine2);
        }
    }
}

예제

다음 예제에서는 연락처 성의 첫 번째 문자로 그룹화된 Contact 개체를 반환합니다. 또한 결과는 성의 첫 번째 문자로 정렬되며 익명 형식으로 프로젝션됩니다.

Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts

    Dim query = ( _
        From contact In contacts _
        Group By firstLetter = contact.LastName.Substring(0, 1) _
        Into contactGroup = Group _
        Select New With {.FirstLetter = firstLetter, .Names = contactGroup}) _
        .OrderBy(Function(letter) letter.FirstLetter)

    For Each n In query
        Console.WriteLine("Last names that start with the letter '{0}':", _
            n.FirstLetter)
        For Each name In n.Names
            Console.WriteLine(name.LastName)
        Next
    Next
End Using

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query = (
        from contact in context.Contacts
        group contact by contact.LastName.Substring(0, 1) into contactGroup
        select new { FirstLetter = contactGroup.Key, Names = contactGroup }).
            OrderBy(letter => letter.FirstLetter);

    foreach (var contact in query)
    {
        Console.WriteLine("Last names that start with the letter '{0}':",
            contact.FirstLetter);
        foreach (var name in contact.Names)
        {
            Console.WriteLine(name.LastName);
        }
    }
}

예제

다음 예제에서는 고객 ID별로 그룹화된 SalesOrderHeader 개체를 반환합니다. 또한 고객별 판매 수량도 반환됩니다.

Using context As New AdventureWorksEntities
    Dim salesOrders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = From order In salesOrders _
                    Group order By order.CustomerID Into idGroup = Group, Count()

    For Each group In query
        Console.WriteLine("Customer ID: {0}", group.CustomerID)
        Console.WriteLine("Order Count: {0}", group.Count)

        For Each sale In group.idGroup
            Console.WriteLine("   Sale ID: {0}", sale.SalesOrderID)
        Next

        Console.WriteLine("")
    Next


End Using

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query = from order in context.SalesOrderHeaders
                group order by order.CustomerID into idGroup 
                select new {CustomerID = idGroup.Key, 
                    OrderCount = idGroup.Count(),
                    Sales = idGroup};

    foreach (var orderGroup in query)
    {
        Console.WriteLine("Customer ID: {0}", orderGroup.CustomerID);
        Console.WriteLine("Order Count: {0}", orderGroup.OrderCount);

        foreach (SalesOrderHeader sale in orderGroup.Sales)
        {
            Console.WriteLine("   Sale ID: {0}", sale.SalesOrderID);
        }

        Console.WriteLine("");
    }
}

참고 항목

개념

LINQ to Entities의 쿼리