Gewusst wie: Gruppieren von Daten (Entity Framework)
In diesem Thema wird gezeigt, wie Abfrageergebnisse gruppiert werden. Das Beispiel gibt einen Satz von geschachtelten Datensätzen zurück, die die Contact.LastName-Spalte enthalten. Diese ist gruppiert und alphabetisch nach dem ersten Buchstaben von Contact.LastName sortiert. Dasselbe Beispiel zeigt die Verwendung der folgenden Entity Framework -Abfragetechnologien:
LINQ-to-Entities
Entity SQL mit ObjectQuery<T>
Abfrage-Generator-Methoden von ObjectQuery<T>
Das Beispiel in diesem Thema beruht auf dem Adventure Works Sales-Modell. Zum Ausführen des Codes in diesem Thema muss dem Projekt bereits das Adventure Works Sales-Modell hinzugefügt und das Projekt zur Verwendung von Entity Framework konfiguriert worden sein. Weitere Informationen finden Sie unter Gewusst wie: Verwenden des Assistenten für Entity Data Model (Entity Framework) bzw. Gewusst wie: Manuelles Konfigurieren eines Entity Framework-Projekts und Gewusst wie: Manuelles Definieren eines Entity Data Model (Entity Framework).
Beispiel
Im Folgenden handelt es sich um das LINQ to Entities -Beispiel.
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);
}
}
}
Im Folgenden handelt es sich um das Entity SQL -Beispiel.
Using context As New AdventureWorksEntities()
Dim esqlQuery As String = "SELECT ln, (SELECT c1.LastName FROM AdventureWorksEntities.Contacts " & _
" AS c1 WHERE SUBSTRING(c1.LastName ,1,1) = ln) AS CONTACT FROM AdventureWorksEntities.Contacts AS c2 " & _
" GROUP BY SUBSTRING(c2.LastName ,1,1) AS ln ORDER BY ln"
For Each rec As DbDataRecord In New ObjectQuery(Of DbDataRecord)(esqlQuery, context)
Console.WriteLine("Last names that start with the letter '{0}':", rec(0))
Dim list As List(Of DbDataRecord) = TryCast(rec(1), List(Of DbDataRecord))
For Each nestedRec As DbDataRecord In list
For i As Integer = 0 To nestedRec.FieldCount - 1
Console.WriteLine(" {0} ", nestedRec(i))
Next
Next
Next
End Using
using (AdventureWorksEntities context =
new AdventureWorksEntities())
{
string esqlQuery = @"SELECT ln,
(SELECT c1.LastName FROM AdventureWorksEntities.Contacts
AS c1 WHERE SUBSTRING(c1.LastName ,1,1) = ln)
AS CONTACT
FROM AdventureWorksEntities.Contacts AS c2 GROUP BY SUBSTRING(c2.LastName ,1,1) AS ln
ORDER BY ln";
foreach (DbDataRecord rec in
new ObjectQuery<DbDataRecord>(esqlQuery, context))
{
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]);
}
}
}
}
Im Folgenden wird ein Beispiel für die Abfrage-Generator-Methode dargestellt.
Using context As New AdventureWorksEntities()
' 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) = _
context.Contacts.GroupBy("SUBSTRING(it.LastName, 1, 1) AS ln", "ln") _
.Select("it.ln AS ln, (SELECT c1.LastName FROM AdventureWorksEntities.Contacts 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) = TryCast(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
Next
Next
End Using
using (AdventureWorksEntities context =
new AdventureWorksEntities())
{
// Define the query with a GROUP BY clause that returns
// a set of nested LastName records grouped by first letter.
ObjectQuery<DbDataRecord> query =
context.Contacts
.GroupBy("SUBSTRING(it.LastName, 1, 1) AS ln", "ln")
.Select("it.ln AS ln, (SELECT c1.LastName " +
"FROM AdventureWorksEntities.Contacts 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]);
}
}
}
}