쿼리 식 구문 예제: 그룹화
이 항목의 예제에서는 쿼리 식 구문을 사용하여 AdventureWorks Sales 모델l을 쿼리하기 위해 GroupBy 메서드를 사용하는 방법을 보여 줍니다. 이 예제에서 사용하는 AdventureWorks Sales 모델에서는 AdventureWorks 샘플 데이터베이스의 Contact, Address, Product, SalesOrderHeader 및 SalesOrderDetail 테이블을 사용합니다.
이 항목의 예제에서는 다음과 같은 using/Imports 문을 사용합니다.
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;
Option Explicit On
Option Strict On
Imports System.Data.Objects
Imports System.Globalization
예 1
다음 예제에서는 우편 번호로 그룹화된 Address 개체를 반환합니다. 결과는 익명 형식으로 프로젝션됩니다.
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);
}
}
}
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
예제 2
다음 예제에서는 연락처 성의 첫 번째 문자로 그룹화된 Contact 개체를 반환합니다. 또한 결과는 성의 첫 번째 문자로 정렬되며 익명 형식으로 프로젝션됩니다.
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);
}
}
}
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
예 3
다음 예제에서는 고객 ID별로 그룹화된 SalesOrderHeader 개체를 반환합니다. 또한 고객별 판매 수량도 반환됩니다.
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("");
}
}
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