Query Expression Syntax Examples: Projection
The examples in this topic demonstrate how to use the Select method and the From … From … keywords to query the AdventureWorks Sales Model using query expression syntax. From … From … is the query based equivalent of the SelectMany method. 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:
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
Select
Example
The following example uses the Select method to return all the rows from the Product table and display the product names.
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
Example
The following example uses Select to return a sequence of only product names.
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
Example
The following example uses the Select method to project the Product.Name and Product.ProductID properties into a sequence of anonymous types.
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)
Example
The following example uses From … From … (the equivalent of the SelectMany method) to select all orders where TotalDue is less than 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
Example
The following example uses From … From … (the equivalent of the SelectMany method) to select all orders where the order was made on October 1, 2002 or later.
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
Example
The following example uses a From … From … (the equivalent of the SelectMany method) to select all orders where the order total is greater than 10000.00 and uses From assignment to avoid requesting the total twice.
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