Ejemplos de sintaxis de consulta basada en métodos: Proyección

Los ejemplos de este tema muestran cómo se utilizan los métodos Select y SelectMany para consultar el modelo AdventureWorks Sales utilizando la sintaxis de consulta basada en métodos. El modelo AdventureWorks Sales que se usa en estos ejemplos se crea a partir de las tablas Contact, Address, Product, SalesOrderHeader y SalesOrderDetail en la base de datos de ejemplo de AdventureWorks.

Los ejemplos de este tema utilizan las siguientes instrucciones using/Imports:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Globalization;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Data.Common;

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

Seleccionar

Ejemplo

En el ejemplo siguiente se usa el método Select para proyectar las propiedades Product.Name y Product.ProductID en una secuencia de tipos anónimos.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query = context.Products
        .Select(product => new
        {
            ProductId = product.ProductID,
            ProductName = product.Name
        });

    Console.WriteLine("Product Info:");
    foreach (var productInfo in query)
    {
        Console.WriteLine("Product Id: {0} Product name: {1} ",
            productInfo.ProductId, productInfo.ProductName);
    }
}

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

Ejemplo

En el ejemplo siguiente se utiliza el método Select para devolver una secuencia de nombres de producto solamente.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    IQueryable<string> productNames = context.Products
        .Select(p => p.Name);

    Console.WriteLine("Product Names:");
    foreach (String productName in productNames)
    {
        Console.WriteLine(productName);
    }
}

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

SelectMany

Ejemplo

En el ejemplo siguiente se utiliza el método SelectMany para seleccionar todos los pedidos en los que TotalDue es inferior a 500,00.

decimal totalDue = 500.00M;
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
    contacts.SelectMany(
        contact => orders.Where(order =>
            (contact.ContactID == order.Contact.ContactID)
                && order.TotalDue < totalDue)
            .Select(order => new
            {
                ContactID = contact.ContactID,
                LastName = contact.LastName,
                FirstName = contact.FirstName,
                OrderID = order.SalesOrderID,
                Total = order.TotalDue
            }));

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

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

Ejemplo

En este ejemplo se utiliza el método SelectMany para seleccionar todos los pedidos efectuados a partir del 1 de octubre de 2002.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
    contacts.SelectMany(
        contact => orders.Where(order =>
            (contact.ContactID == order.Contact.ContactID)
                && order.OrderDate >= new DateTime(2002, 10, 1))
            .Select(order => new
            {
                ContactID = contact.ContactID,
                LastName = contact.LastName,
                FirstName = contact.FirstName,
                OrderID = order.SalesOrderID,
                OrderDate = order.OrderDate
            }));

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

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

Consulte también

• Consultas en LINQ to Entities