Method-Based Query Syntax Examples: Projection

 

The examples in this topic demonstrate how to use the Select and SelectMany methodsto query the AdventureWorks Sales Model using method-based query syntax. The AdventureWorks Sales Model used in these examples is built from the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.

The examples in this topic use the following using/Imports statements:

Option Explicit On
Option Strict On
Imports System.Data.Objects
Imports System.Globalization

The following example uses the Select(Of TSource, TResult) method to project the Product.Name and Product.ProductID properties into a sequence of anonymous types.

Using context As New AdventureWorksEntities
    Dim query = context.Products _
    .Select(Function(prod) New With _
    { _
        .ProductName = prod.Name, _
        .ProductId = prod.ProductID _
    })

    Console.WriteLine("Product Info:")
    For Each productInfo In query
        Console.WriteLine("Product Id: {0} Product name: {1} ", _
                productInfo.ProductId, productInfo.ProductName)
    Next
End Using

The following example uses the Select method to return a sequence of only product names.

Using context As New AdventureWorksEntities
    Dim productNames = context.Products _
    .Select(Function(p) p.Name())

    Console.WriteLine("Product Names:")
    For Each productName In productNames
        Console.WriteLine(productName)
    Next
End Using

The following example uses the SelectMany method to select all orders where TotalDue is less than 500.00.

Dim totalDue = 500D
Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = contacts.SelectMany( _
        Function(contact) orders.Where(Function(order) _
            (contact.ContactID = order.Contact.ContactID) _
                    And order.TotalDue < totalDue) _
            .Select(Function(order) New With _
            { _
                .ContactID = contact.ContactID, _
                .LastName = contact.LastName, _
                .FirstName = contact.FirstName, _
                .OrderID = order.SalesOrderID, _
                .Total = order.TotalDue _
            }) _
    )

    For Each smallOrder In query
        Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Total Due: ${4} ", _
            smallOrder.ContactID, smallOrder.LastName, smallOrder.FirstName, _
            smallOrder.OrderID, smallOrder.Total)
    Next
End Using

The following example uses the SelectMany method to select all orders where the order was made on October 1, 2002 or later.

Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = contacts.SelectMany( _
        Function(contact) orders.Where(Function(order) _
            (contact.ContactID = order.Contact.ContactID) _
                    And order.OrderDate >= New DateTime(2002, 10, 1)) _
            .Select(Function(order) New With _
            { _
                .ContactID = contact.ContactID, _
                .LastName = contact.LastName, _
                .FirstName = contact.FirstName, _
                .OrderID = order.SalesOrderID, _
                .OrderDate = order.OrderDate _
            }) _
    )

    For Each order In query
        Console.WriteLine("Contact ID: {0} Name: {1}, {2} Order ID: {3} Order date: {4:d} ", _
            order.ContactID, order.LastName, order.FirstName, _
            order.OrderID, order.OrderDate)
    Next
End Using
Show: