Sorgu İfadesi Söz Dizimi Örnekleri: Gruplandırma
Bu konudaki örneklerde, sorgu ifadesi söz dizimini GroupBy
kullanarak AdventureWorks Satış Modeli'ni sorgulamak için yönteminin nasıl kullanılacağı gösterilmektedir. Bu örneklerde kullanılan AdventureWorks Sales modeli, AdventureWorks örnek veritabanındaki Contact, Address, Product, SalesOrderHeader ve SalesOrderDetail tablolarından oluşturulur.
Bu konudaki örneklerde aşağıdaki using
/Imports
deyimler kullanılır:
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
Örnek 1
Aşağıdaki örnek, posta koduna göre gruplandırılmış nesneleri döndürür Address
. Sonuçlar anonim bir türe yansıtılır.
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
Örnek 2
Aşağıdaki örnek, kişinin soyadının ilk harfine göre gruplandırılmış nesneleri döndürür Contact
. Sonuçlar da soyadının ilk harfine göre sıralanır ve anonim bir türe yansıtılır.
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
Örnek 3
Aşağıdaki örnek, müşteri kimliğine göre gruplandırılmış nesneleri döndürür SalesOrderHeader
. Her müşteri için satış sayısı da döndürülür.
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