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

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

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

Option Explicit On
Option Strict On
Imports L2EExamplesVB.AdventureWorksModel
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 AdventureWorksModel;
using System.Globalization;

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

예제

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

Using AWEntities As New AdventureWorksEntities
    Dim addresses As ObjectQuery(Of Address) = AWEntities.Address

    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 AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Address> addresses = AWEntities.Address;

    var query =
        from address in 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 AWEntities As New AdventureWorksEntities
    Dim contacts As ObjectQuery(Of Contact) = AWEntities.Contact

    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 AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Contact> contacts = AWEntities.Contact;

    var query = (
        from contact in 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 AWEntities As New AdventureWorksEntities
    Dim salesOrders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader

    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 AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<SalesOrderHeader> salesOrders = AWEntities.SalesOrderHeader;

    var query = from order in salesOrders
                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)