Gewusst wie: Ausführen einer Abfrage, die einen primitiven Typ zurückgibt (Entity Framework)

In diesem Thema wird anhand von Beispielen gezeigt, wie Abfragen ausgeführt werden, die einen primitiven Typ zurückgeben. Das gleiche Beispiel wird unter Verwendung jeder der folgenden Entity Framework-Abfragetechnologien dargestellt:

• 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 für die Verwendung von Entity Framework konfiguriert haben. Führen Sie dazu die Verfahren unter Gewusst wie: Manuelles Konfigurieren eines Entity Framework-Projekts und Gewusst wie: Manuelles Definieren eines Entity Data Model (Entity Framework) aus. 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

Im Folgenden finden Sie das LINQ-to-Entities-Beispiel:

Dim contactId As Integer = 377

Using advWorksContext As New AdventureWorksEntities
    Try
        'Select a value.
        Dim orders As ObjectQuery(Of SalesOrderHeader) _
            = advWorksContext.SalesOrderHeader

        Dim orderQuery = _
        From order In orders _
        Where order.Contact.ContactID = contactId _
        Select order.PurchaseOrderNumber.Length

        ' Iterate through the collection of values.
        For Each result In orderQuery
            Console.WriteLine("{0}", result)
        Next

        ' Use a nullable DateTime value because ShipDate can be null.
        Dim shipDateQuery As IQueryable(Of Date?) = _
        From order In orders _
        Where order.Contact.ContactID = contactId _
        Select order.ShipDate

        ' Iterate through the collection of values.
        For Each shipDate In shipDateQuery
            Dim shipDateMessage As String = "date not set"

            If Not shipDate = Nothing Then
                shipDateMessage = shipDate.ToString()
            End If
            Console.WriteLine("Ship Date: {0}.", shipDateMessage)
        Next
    Catch ex As EntitySqlException
        Console.WriteLine(ex.ToString())
    Catch ex As InvalidOperationException
        Console.WriteLine(ex.ToString())
    End Try
End Using

int contactId = 377;

using (AdventureWorksEntities advWorksContext 
    = new AdventureWorksEntities())
{
    try
    {
        // Select a value.
        ObjectQuery<SalesOrderHeader> orders 
            = advWorksContext.SalesOrderHeader;
        
        IQueryable<Int32> orderQuery = 
            from order in orders                   
            where order.Contact.ContactID == contactId 
            select order.PurchaseOrderNumber.Length;

        // Iterate through the collection of values.
        foreach (Int32 result in orderQuery)
        {
            Console.WriteLine("{0}", result);
        }
        
        // Use a nullable DateTime value because ShipDate can be null.
        IQueryable<DateTime?> shipDateQuery =
            from order in orders
            where order.Contact.ContactID == contactId
            select order.ShipDate;

        // Iterate through the collection of values.
        foreach (DateTime? shipDate in shipDateQuery)
        {
            string shipDateMessage = "date not set";

            if (shipDate != null)
            {
                shipDateMessage = shipDate.ToString();
            }
            Console.WriteLine("Ship Date: {0}.", shipDateMessage);
        }
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

Im Folgenden ist das Entity SQL-Beispiel dargestellt:

Dim contactId As Integer = 377

Using advWorksContext As New AdventureWorksEntities 
    Dim orderQueryString As String = "SELECT VALUE Length(order.PurchaseOrderNumber) " & _
        "FROM AdventureWorksEntities.SalesOrderHeader AS order " & _
        "WHERE order.CustomerID = @contactId"
    Dim shipDateQueryString As String = "SELECT VALUE order.ShipDate " & _
        "FROM AdventureWorksEntities.SalesOrderHeader AS order " & _
        "WHERE order.CustomerID = @contactId"

    Try
        ' Use the SelectValue method to select a value.
        Dim orderQuery As ObjectQuery(Of Int32) = _
            New ObjectQuery(Of Int32)(orderQueryString, _
                advWorksContext, MergeOption.NoTracking)
        orderQuery.Parameters.Add( _
            New ObjectParameter("contactId", contactId))

        ' Iterate through the collection of values.
        For Each result In orderQuery
            Console.WriteLine("{0}", result)
        Next

        ' Use a nullable DateTime value because ShipDate can be null.
        Dim shipDateQuery As ObjectQuery(Of Nullable(Of Date)) = _
            New ObjectQuery(Of Nullable(Of Date))(shipDateQueryString, _
        advWorksContext, MergeOption.NoTracking)
        shipDateQuery.Parameters.Add( _
            New ObjectParameter("contactId", contactId))

        ' Iterate through the collection of values.
        For Each shipDate In shipDateQuery
            Dim shipDateMessage As String = "date not set"

            If Not shipDate = Nothing Then
                shipDateMessage = shipDate.ToString()
            End If
            Console.WriteLine("Ship Date: {0}.", shipDateMessage)
        Next
    Catch ex As EntityException
        Console.WriteLine(ex.ToString())
    Catch ex As InvalidOperationException
        Console.WriteLine(ex.ToString())
    End Try
End Using

int contactId = 377;

using (AdventureWorksEntities advWorksContext =
    new AdventureWorksEntities())
{
    string orderQueryString = @"SELECT VALUE Length(order.PurchaseOrderNumber)
        FROM AdventureWorksEntities.SalesOrderHeader AS order
        WHERE order.CustomerID = @contactId";
    string shipDateQueryString = @"SELECT VALUE order.ShipDate
        FROM AdventureWorksEntities.SalesOrderHeader AS order
        WHERE order.CustomerID = @contactId";

    try
    {
        // Use the SelectValue method to select a value.
        ObjectQuery<Int32> orderQuery =
            new ObjectQuery<Int32>(orderQueryString, 
                advWorksContext, MergeOption.NoTracking);
        orderQuery.Parameters.Add(
            new ObjectParameter("contactId", contactId));

        // Iterate through the collection of values.
        foreach (Int32 result in orderQuery)
        {
            Console.WriteLine("{0}", result);
        }

        // Use a nullable DateTime value because ShipDate can be null.
        ObjectQuery<Nullable<DateTime>> shipDateQuery =
            new ObjectQuery<Nullable<DateTime>>(shipDateQueryString,
        advWorksContext, MergeOption.NoTracking);
        shipDateQuery.Parameters.Add(                    
            new ObjectParameter("contactId", contactId));

        // Iterate through the collection of values.
        foreach (Nullable<DateTime> shipDate in shipDateQuery)
        {
            string shipDateMessage = "date not set";

            if (shipDate != null)
            {
                shipDateMessage = shipDate.ToString();
            }
            Console.WriteLine("Ship Date: {0}.", shipDateMessage);
        }
    }
    catch (EntityException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

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

Dim contactId As Integer = 377

Using advWorksContext As New AdventureWorksEntities
    Try
        ' Use the SelectValue method to select a value.
        Dim orderQuery As ObjectQuery(Of Int32) = _
        advWorksContext.SalesOrderHeader _
            .Where("it.CustomerID = @contactId", _
                New ObjectParameter("contactId", contactId)) _
            .SelectValue(Of Int32)("Length(it.PurchaseOrderNumber)")

        ' Iterate through the collection of values.
        For Each result In orderQuery
            Console.WriteLine("{0}", result)
        Next

        ' Use a nullable DateTime value because ShipDate can be null.
        Dim shipDateQuery As ObjectQuery(Of Nullable(Of Date)) = _
        advWorksContext.SalesOrderHeader _
            .Where("it.CustomerID = @contactId", _
                New ObjectParameter("contactId", contactId)) _
            .SelectValue(Of Nullable(Of Date))("it.ShipDate")

        ' Iterate through the collection of values.
        For Each shipDate In shipDateQuery
            Dim shipDateMessage As String = "date not set"
            If Not shipDate = Nothing Then
                shipDateMessage = shipDate.ToString()
            End If
            Console.WriteLine("Ship Date: {0}.", shipDateMessage)
        Next
    Catch ex As EntitySqlException
        Console.WriteLine(ex.ToString())
    Catch ex As InvalidOperationException
        Console.WriteLine(ex.ToString())
    End Try
End Using

int contactId = 377;

using (AdventureWorksEntities advWorksContext =
    new AdventureWorksEntities())
{
    try
    {
        // Use the SelectValue method to select a value.
        ObjectQuery<Int32> orderQuery =
            advWorksContext.SalesOrderHeader 
            .Where("it.CustomerID = @contactId", 
            new ObjectParameter("contactId", contactId))
            .SelectValue<Int32>("Length(it.PurchaseOrderNumber)");

        // Iterate through the collection of values.
        foreach (Int32 result in orderQuery)
        {
            Console.WriteLine("{0}", result);
        }

        // Use a nullable DateTime value because ShipDate can be null.
        ObjectQuery<Nullable<DateTime>> shipDateQuery =
            advWorksContext.SalesOrderHeader
            .Where("it.CustomerID = @contactId",
                new ObjectParameter("contactId", contactId))
            .SelectValue<Nullable<DateTime>>("it.ShipDate");
        
        // Iterate through the collection of values.
        foreach (Nullable<DateTime> shipDate in shipDateQuery)
        {
            string shipDateMessage = "date not set";

            if (shipDate != null)
            {
                shipDateMessage = shipDate.ToString();
            }
            Console.WriteLine("Ship Date: {0}.", shipDateMessage);
        }
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

Siehe auch

Aufgaben

Gewusst wie: Ausführen einer Abfrage, die einen Entitätstyp zurückgibt (Entity Framework)
Gewusst wie: Ausführen einer Abfrage, die einen anonymen Typ zurückgibt (Entity Framework)
Gewusst wie: Ausführen einer parametrisierten Abfrage (Entity Framework)

Konzepte

Abfrage-Generator-Methoden (Entity Framework)

Weitere Ressourcen

Abfragen eines Entity Data Model (Entity Framework-Aufgaben)