Ejemplos de sintaxis de expresiones de consultas: proyección (LINQ to Entities)
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 de la base de datos de ejemplo AdventureWorks.
Los ejemplos de este tema usan las instrucciones using/Imports siguientes:
Option Explicit On
Option Strict On
Imports System.Data.Objects
Imports System.Globalization
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;
Select
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 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
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);
}
}
Ejemplo
El ejemplo siguiente utiliza Select para devolver una secuencia de nombres de producto solamente.
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
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);
}
}
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 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
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);
}
}
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.
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
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);
}
}
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 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
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);
}
}
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.
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
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);
}
}