Procédure : regrouper des données (Entity Framework)
Cette rubrique montre comment regrouper des résultats de requête. L'exemple retourne un ensemble d'enregistrements de données imbriqués qui contiennent la colonne Contact.LastName, regroupés et triés par ordre alphabétique selon la première lettre de Contact.LastName. Le même exemple est repris en utilisant chacune des technologies de requête Entity Framework suivantes :
LINQ to Entities
Entity SQL avec ObjectQuery<T>
Méthodes du Générateur de requêtes d'ObjectQuery<T>
Les exemples de cette rubrique sont basés sur le modèle de vente Adventure Works. Pour exécuter le code de cet exemple, vous devez déjà avoir ajouté le modèle de vente AdventureWorks à votre projet et configuré ce dernier pour qu'il utilise Entity Framework. Pour ce faire, exécutez les procédures décrites dans Procédure : configurer manuellement un projet Entity Framework et Procédure : définir manuellement un modèle EDM (Entity Data Model) (Entity Framework). Vous pouvez également définir le modèle de vente AdventureWorks Sales Model à l'aide de l'Assistant EDM. Pour plus d'informations, voir Procédure : utiliser l'Assistant Entity Data Model (Entity Framework).
Exemple
L'exemple ci-dessous utilise 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);
}
}
}
L'exemple ci-dessous utilise Entité 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());
}
}
L'exemple ci-dessous utilise la méthode du Générateur de requêtes.
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());
}
}