Sdílet prostřednictvím


How to: Execute a Query that Returns a Collection of Primitive Types

This topic provides examples of how to execute queries that return a collection of primitive types. To return just a single object, use one of the following methods on top of your query: First, FirstOrDefault, Single, SingleOrDefault.

The same example is shown using each of the following Entity Framework query technologies:

  • LINQ to Entities

  • Entity SQL with ObjectQuery<T>

  • Query builder methods of ObjectQuery<T>

The example in this topic is based on the Adventure Works Sales Model. To run the code in this topic, you must have already added the Adventure Works Sales Model to your project and configured your project to use the Entity Framework. For more information, see How to: Use the Entity Data Model Wizard (Entity Framework) or How to: Manually Configure an Entity Framework Project and How to: Manually Define an Entity Data Model (Entity Framework).

Example

The following is the LINQ to Entities example.

Dim contactId As Integer = 377

Using context As New AdventureWorksEntities()
    ' Select a value. 
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim orderQuery As IQueryable(Of Int32) = From order In orders _
        Where order.Contact.ContactID = contactId _
        Select order.PurchaseOrderNumber.Length

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

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

    ' Iterate through the collection of values. 
    For Each shipDate As System.Nullable(Of DateTime) In shipDateQuery
        Dim shipDateMessage As String = "date not set"

        If shipDate IsNot Nothing Then
            shipDateMessage = shipDate.ToString()
        End If
        Console.WriteLine("Ship Date: {0}.", shipDateMessage)
    Next
End Using
int contactId = 377;

using (AdventureWorksEntities context
    = new AdventureWorksEntities())
{
    // Select a value.
    ObjectSet<SalesOrderHeader> orders
        = context.SalesOrderHeaders;

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

The following is the Entity SQL example.

Dim contactId As Integer = 377

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

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

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

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

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

        If shipDate IsNot Nothing Then
            shipDateMessage = shipDate.ToString()
        End If
        Console.WriteLine("Ship Date: {0}.", shipDateMessage)
    Next
End Using
int contactId = 377;

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

    // Use the SelectValue method to select a value.
    ObjectQuery<Int32> orderQuery =
        new ObjectQuery<Int32>(orderQueryString,
            context, 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,
    context, 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);
    }
}

The following is the query builder method example.

Dim contactId As Integer = 377

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

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

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

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

        If shipDate IsNot Nothing Then
            shipDateMessage = shipDate.ToString()
        End If
        Console.WriteLine("Ship Date: {0}.", shipDateMessage)
    Next
End Using
int contactId = 377;

using (AdventureWorksEntities context =
    new AdventureWorksEntities())
{
    // Use the SelectValue method to select a value.
    ObjectQuery<Int32> orderQuery =
        context.SalesOrderHeaders
        .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 =
        context.SalesOrderHeaders
        .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);
    }
}

See Also

Tasks

How to: Execute a Query that Returns Entity Type Objects
How to: Execute a Query that Returns Anonymous Type Objects
How to: Execute a Parameterized Query

Concepts

Query Builder Methods