Procedura: raggruppare dati (Entity Framework)

In questo argomento viene descritto come raggruppare i risultati di una query. Nell'esempio viene restituito un set di record di dati nidificati che contengono la colonna Contact.LastName, raggruppati e ordinati alfabeticamente in base alla prima lettera di Contact.LastName. Lo stesso esempio viene illustrato utilizzando ognuna delle tecnologie di query Entity Framework seguenti:

• LINQ to Entities

• Entity SQL con ObjectQuery<T>

• Metodi del generatore di query di ObjectQuery<T>

L'esempio incluso in questo argomento è basato sul modello Sales di AdventureWorks. Per eseguire il codice incluso in questo argomento, è necessario avere già aggiunto il modello Sales di AdventureWorks al progetto e avere configurato il progetto per l'utilizzo di Entity Framework. Per ulteriori informazioni, vedere Procedura: utilizzare la Procedura guidata Entity Data Model (Entity Framework) o Procedura: configurare manualmente un progetto di Entity Framework e Procedura: definire manualmente un modello EDM (Entity Framework).

Esempio

Di seguito viene fornito un esempio di utilizzo di 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);
        }
    }
}

Di seguito viene fornito un esempio di utilizzo di 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]);
            }
        }
    }
}

Di seguito viene fornito un esempio del metodo del generatore di query.

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]);
            }
        }
    }
}

Vedere anche

Concetti

Esecuzione di query su un modello concettuale (Entity Framework)