Home Library Learn Downloads Support Community
Sign in | United States - English | |
0 out of 4 rated this helpful - Rate this topic

Method-Based Query Syntax Examples: Projection

.NET Framework 4

The examples in this topic demonstrate how to use the Select and SelectMany methodsto query the AdventureWorks Sales Model using method-based query syntax. 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:

C#
VB
Copy 
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 project the Product.Name and Product.ProductID properties into a sequence of anonymous types.

C#
VB
Copy 
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query = context.Products
        .Select(product => 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);
    }
}

Example

The following example uses the Select method to return a sequence of only product names.

C#
VB
Copy 
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    IQueryable<string> productNames = context.Products
        .Select(p => p.Name);

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

SelectMany

Example

The following example uses the SelectMany method to select all orders where TotalDue is less than 500.00.

C#
VB
Copy 
decimal totalDue = 500.00M;
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
    contacts.SelectMany(
        contact => orders.Where(order =>
            (contact.ContactID == order.Contact.ContactID)
                && order.TotalDue < totalDue)
            .Select(order => 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 the SelectMany method to select all orders where the order was made on October 1, 2002 or later.

C#
VB
Copy 
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
    contacts.SelectMany(
        contact => orders.Where(order =>
            (contact.ContactID == order.Contact.ContactID)
                && order.OrderDate >= new DateTime(2002, 10, 1))
            .Select(order => 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);
    }
}

See Also

Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
Errors

The following using statements arte not required:

System.Data
System.Collections.Generic
System.Text
System.Globalization
System.Data.EntityClient
System.Data.SqlClient
System.Data.Common

Also, using the directions as stated on MSDN to create the necessary edmx file (verified as correct), the code in the first example wil not execute giving the following errors:

'System.Data.Common.DbDataRecord' does not contain a definition for 'ProductId' and no extension method 'ProductId' accepting a first argument of type 'System.Data.Common.DbDataRecord' could be found

'System.Data.Common.DbDataRecord' does not contain a definition for 'ProductName' and no extension method 'ProductName' accepting a first argument of type 'System.Data.Common.DbDataRecord' could be found

Cannot convert lambda expression to type 'string' because it is not a delegate type

Consider offering the examples within a downloadable project containing all samples and necessary edmx, etc.

History