Export (0) Print
Expand All

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;
    

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);
}
}
    

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);
}
}
    

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);
}
}
    

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);
}
}
    

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);
}
}
    

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);
}
}
    

See Also




Build Date:

2012-10-01
Show:
© 2014 Microsoft