Ejemplos de sintaxis de expresiones de consulta: Proyección

Los ejemplos de este tema muestran cómo usar el método Select y las palabras clave From … From … para consultar el modelo AdventureWorks Sales con la sintaxis de las expresiones de consulta. From … From … es el equivalente basado en las consultas del método SelectMany. 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 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 devolver todas las filas de la tabla Product y mostrar los nombres de producto.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    IQueryable<Product> productsQuery = from product in context.Products
                                        select product;

    Console.WriteLine("Product Names:");
    foreach (var prod in productsQuery)
    {
        Console.WriteLine(prod.Name);
    }
}

Using context As New AdventureWorksEntities
    Dim products As ObjectSet(Of Product) = context.Products

    Dim productsQuery = _
        From product In products _
        Select product

    Console.WriteLine("Product Names:")
    For Each product In productsQuery
        Console.WriteLine(product.Name)
    Next
End Using

Ejemplo

El ejemplo siguiente utiliza Select para devolver una secuencia de nombres de producto solamente.

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

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

Using context As New AdventureWorksEntities
    Dim products As ObjectSet(Of Product) = context.Products

    Dim productNames = _
        From p In products _
        Select p.Name

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

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 =
        from product in context.Products
        select 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 products As ObjectSet(Of Product) = context.Products

    Dim query = _
        From product In products _
        Select New With _
        { _
            .ProductId = product.ProductID, _
            .ProductName = product.Name _
        }

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

From … From … (SelectMany)

Ejemplo

En el ejemplo siguiente se usa From … From … (el equivalente del 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 =
        from contact in contacts
        from order in orders
        where contact.ContactID == order.Contact.ContactID
            && order.TotalDue < totalDue
        select 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 = _
        From contact In contacts _
        From order In orders _
        Where contact.ContactID = order.Contact.ContactID _
                And order.TotalDue < totalDue _
        Select 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 el ejemplo siguiente se utiliza From … From … (el equivalente del 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 =
        from contact in contacts
        from order in orders
        where contact.ContactID == order.Contact.ContactID
            && order.OrderDate >= new DateTime(2002, 10, 1)
        select 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 = _
        From contact In contacts _
        From order In orders _
        Where contact.ContactID = order.Contact.ContactID _
                And order.OrderDate >= New DateTime(2002, 10, 1) _
        Select 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

Ejemplo

En el ejemplo siguiente se utiliza From … From … (el equivalente al método SelectMany) para seleccionar todos los pedidos en los que el total del pedido es superior a 10000,00 y se utiliza la asignación From para evitar que se solicite dos veces el total.

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

    var query =
        from contact in contacts
        from order in orders
        let total = order.TotalDue
        where contact.ContactID == order.Contact.ContactID
            && total >= totalDue
        select new
        {
            ContactID = contact.ContactID,
            LastName = contact.LastName,
            OrderID = order.SalesOrderID,
            total
        };

    foreach (var order in query)
    {
        Console.WriteLine("Contact ID: {0} Last name: {1} Order ID: {2} Total: {3}",
            order.ContactID, order.LastName, order.OrderID, order.total);
    }
}

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

    Dim query = _
        From contact In contacts _
        From order In orders _
        Let total = order.TotalDue _
        Where contact.ContactID = order.Contact.ContactID _
                And total >= totalDue _
        Select New With _
        { _
            .ContactID = contact.ContactID, _
            .LastName = contact.LastName, _
            .OrderID = order.SalesOrderID, _
            total _
        }

    For Each order In query
        Console.WriteLine("Contact ID: {0} Last name: {1} Order ID: {2} Total: {3}", _
                order.ContactID, order.LastName, order.OrderID, order.total)
    Next
End Using

Consulte también

• Consultas en LINQ to Entities