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 wird unter Verwendung jeder der folgenden Entity Framework-Abfragetechnologien gezeigt:

• LINQ to Entities

• Entity SQL mit ObjectQuery<T>

• Abfrage-Generator-Methoden von ObjectQuery<T>

Die Beispiele in diesem Thema beruhen auf dem AdventureWorks Sales-Modell. Um den Code in diesem Beispiel auszuführen, müssen Sie Ihrem Projekt bereits das "AdventureWorks Sales"-Modell hinzugefügt und das Projekt zur Verwendung von Entity Framework konfiguriert haben. Verwenden Sie dazu die Verfahren in Gewusst wie: Manuelles Konfigurieren eines Entity Framework-Projekts und Gewusst wie: Manuelles Definieren eines Entity Data Model (Entity Framework). Sie können auch den Assistenten für Entity Data Model zum Definieren des "AdventureWorks Sales"-Modells verwenden. Weitere Informationen finden Sie unter Gewusst wie: Verwenden des Assistenten für Entity Data Model (Entity Framework).

Beispiel

Es folgt das LINQ-to-Entities-Beispiel:

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

Es folgt das Entity SQL-Beispiel:

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

Im Folgenden wird ein Beispiel für die Abfrage-Generator-Methode dargestellt.

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

Siehe auch

Weitere Ressourcen

Abfragen eines Entity Data Model (Entity Framework-Aufgaben)