データをグループ化する方法 (Entity Framework)
このトピックでは、クエリ結果をグループ化する方法について説明します。この例は、Contact.LastName 列を含む入れ子になったデータ レコードを返します。データ レコードは、Contact.LastName の先頭文字でグループ化され、アルファベット順に並べ替えられています。次の各 エンティティ フレームワーク クエリ テクノロジを使って同じことを行う例が紹介されています。
LINQ to Entities
Entity SQL と ObjectQuery<T>
ObjectQuery<T> のクエリ ビルダ メソッド
このトピックの例には、Adventure Works Sales Model が使用されています。この例のコードを実行するには、あらかじめプロジェクトに AdventureWorks Sales Model を追加し、Entity Framework を使用するようにプロジェクトを構成しておく必要があります。具体的な方法については、「Entity Framework プロジェクトを手動で構成する方法」および「Entity Data Model を手動で定義する方法 (Entity Framework)」の手順を参照してください。Entity Data Model ウィザードを使用して、AdventureWorks Sales Model を定義することもできます。詳細については、「Entity Data Model ウィザードを使用する方法 (Entity Framework)」を参照してください。
例
以下は LINQ to Entities の例です。
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);
}
}
}
以下は Entity SQL の例です。
using (AdventureWorksEntities advWorksContext =
new AdventureWorksEntities())
{
string esqlQuery = @"SELECT ln,
(SELECT c1.LastName FROM AdventureWorksEntities.Contact
AS c1 WHERE SUBSTRING(c1.LastName ,1,1) = ln)
AS CONTACT
FROM AdventureWorksEntities.CONTACT AS c2 GROUP BY SUBSTRING(c2.LastName ,1,1) AS ln
ORDER BY ln";
try
{
foreach (DbDataRecord rec in
new ObjectQuery<DbDataRecord>(esqlQuery, advWorksContext))
{
Console.WriteLine("Last names that start with the letter '{0}':",
rec[0]);
List<DbDataRecord> list = rec[1] as List<DbDataRecord>;
foreach (DbDataRecord nestedRec in list)
{
for (int i = 0; i < nestedRec.FieldCount; i++)
{
Console.WriteLine(" {0} ", nestedRec[i]);
}
}
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.ToString());
}
}
以下は、クエリ ビルダ メソッドの例です。
Using advWorksContext As New AdventureWorksEntities()
Try
' Define the query with a GROUP BY clause that returns
' a set of nested LastName records grouped by first letter.
Dim query As ObjectQuery(Of DbDataRecord) = _
advWorksContext.Contact _
.GroupBy("SUBSTRING(it.LastName, 1, 1) AS ln", "ln") _
.Select("it.ln AS ln, (SELECT c1.LastName " + _
"FROM AdventureWorksEntities.Contact AS c1 " + _
"WHERE SubString(c1.LastName, 1, 1) = it.ln) AS CONTACT") _
.OrderBy("it.ln")
' Execute the query and walk through the nested records.
For Each rec As DbDataRecord In query.Execute(MergeOption.AppendOnly)
Console.WriteLine("Last names that start with the letter '0':", _
rec(0))
Dim list As List(Of DbDataRecord) = CType(rec(1), List(Of DbDataRecord))
For Each r As DbDataRecord In list
For i As Integer = 0 To r.FieldCount - 1
Console.WriteLine(" {0}", r(i))
Next i
Next
Next
Catch ex As EntitySqlException
Console.WriteLine(ex.ToString())
End Try
End Using
using (AdventureWorksEntities advWorksContext =
new AdventureWorksEntities())
{
try
{
// Define the query with a GROUP BY clause that returns
// a set of nested LastName records grouped by first letter.
ObjectQuery<DbDataRecord> query =
advWorksContext.Contact
.GroupBy("SUBSTRING(it.LastName, 1, 1) AS ln", "ln")
.Select("it.ln AS ln, (SELECT c1.LastName " +
"FROM AdventureWorksEntities.Contact AS c1 " +
"WHERE SubString(c1.LastName, 1, 1) = it.ln) AS CONTACT")
.OrderBy("it.ln");
// Execute the query and walk through the nested records.
foreach (DbDataRecord rec in
query.Execute(MergeOption.AppendOnly))
{
Console.WriteLine("Last names that start with the letter '{0}':",
rec[0]);
List<DbDataRecord> list = rec[1] as List<DbDataRecord>;
foreach (DbDataRecord r in list)
{
for (int i = 0; i < r.FieldCount; i++)
{
Console.WriteLine(" {0} ", r[i]);
}
}
}
}
catch (EntitySqlException ex)
{
Console.WriteLine(ex.ToString());
}
}