Query Expression Syntax Examples: Projection (LINQ to Entities)
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:
Option Explicit On
Option Strict On
Imports L2EExamplesVB.AdventureWorksModel
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 AdventureWorksModel;
using System.Globalization;
For more information, see How to: Create a LINQ to Entities Project in Visual Studio.
Select
Example
The following example uses the Select method to return all the rows from the Product table and display the product names.
Using AWEntities As New AdventureWorksEntities
Dim products As ObjectQuery(Of Product) = AWEntities.Product
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 AWEntities = new AdventureWorksEntities())
{
ObjectQuery<Product> products = AWEntities.Product;
IQueryable<Product> productsQuery = from product in products
select product;
Console.WriteLine("Product Names:");
foreach (var prod in productsQuery)
{
Console.WriteLine(prod.Name);
}
}
Example
The following example uses Select to return a sequence of only product names.
Using AWEntities As New AdventureWorksEntities
Dim products As ObjectQuery(Of Product) = AWEntities.Product
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 AWEntities = new AdventureWorksEntities())
{
ObjectQuery<Product> products = AWEntities.Product;
IQueryable<string> productNames =
from p in products
select p.Name;
Console.WriteLine("Product Names:");
foreach (String productName in productNames)
{
Console.WriteLine(productName);
}
}
Example
The following example uses the Select method to project the Product.Name and Product.ProductID properties into a sequence of anonymous types.
Using AWEntities As New AdventureWorksEntities
Dim products As ObjectQuery(Of Product) = AWEntities.Product
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 AWEntities = new AdventureWorksEntities())
{
ObjectQuery<Product> products = AWEntities.Product;
var query =
from product in 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)
Example
The following example uses From … From … (the equivalent of the SelectMany method) to select all orders where TotalDue is less than 500.00.
Using AWEntities As New AdventureWorksEntities
Dim contacts As ObjectQuery(Of Contact) = AWEntities.Contact
Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader
Dim query = _
From contact In contacts _
From order In orders _
Where contact.ContactID = order.Contact.ContactID _
And order.TotalDue < 500D _
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
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
ObjectQuery<Contact> contacts = AWEntities.Contact;
ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;
var query =
from contact in contacts
from order in orders
where contact.ContactID == order.Contact.ContactID
&& order.TotalDue < 500.00M
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);
}
}
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 AWEntities As New AdventureWorksEntities
Dim contacts As ObjectQuery(Of Contact) = AWEntities.Contact
Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader
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 AWEntities = new AdventureWorksEntities())
{
ObjectQuery<Contact> contacts = AWEntities.Contact;
ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;
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);
}
}
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.
Using AWEntities As New AdventureWorksEntities
Dim contacts As ObjectQuery(Of Contact) = AWEntities.Contact
Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader
Dim query = _
From contact In contacts _
From order In orders _
Let total = order.TotalDue _
Where contact.ContactID = order.Contact.ContactID _
And total >= 10000D _
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
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
ObjectQuery<Contact> contacts = AWEntities.Contact;
ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;
var query =
from contact in contacts
from order in orders
let total = order.TotalDue
where contact.ContactID == order.Contact.ContactID
&& total >= 10000.0M
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);
}
}