Cómo: Agrupar datos (Entity Framework)
En este tema se describe cómo agrupar los resultados de una consulta. En el ejemplo se devuelve un conjunto de registros de datos anidados que contienen la columna Contact.LastName, agrupados y ordenados alfabéticamente por la primera letra de Contact.LastName. El mismo ejemplo se muestra con cada una de las siguientes tecnologías de consulta de Entity Framework :
LINQ to Entities
Entity SQL con ObjectQuery<T>
Métodos del generador de consultas de ObjectQuery<T>
El ejemplo de este tema se basa en el modelo AdventureWorks Sales. Para ejecutar el código de este tema, debe haber agregado el modelo AdventureWorks Sales al proyecto y haber configurado el proyecto para que use Entity Framework. Para obtener más información, vea Cómo: Usar el Asistente para Entity Data Model (Entity Framework) o Cómo: Configurar manualmente un proyecto de Entity Framework y Cómo: Definir manualmente un modelo Entity Data Model (Entity Framework).
Ejemplo
A continuación se muestra el ejemplo de LINQ to Entities .
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);
}
}
}
A continuación se muestra el ejemplo de Entity SQL .
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]);
}
}
}
}
A continuación se muestra el ejemplo del método del generador de consultas.
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]);
}
}
}
}