Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Query Expression Syntax Examples: Projection (LINQ to DataSet)

The examples in this topic demonstrate how to use the Select and SelectMany methods to query a DataSet using the query expression syntax.

The FillDataSet method used in these examples is specified in Loading Data Into a DataSet.

The examples in this topic use 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.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Globalization;

For more information, see How to: Create a LINQ to DataSet Project In Visual Studio.

This example uses the Select method to return all the rows from the Product table and display the product names.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable products = ds.Tables["Product"];

IEnumerable<DataRow> query =
    from product in products.AsEnumerable()
    select product;

Console.WriteLine("Product Names:");
foreach (DataRow p in query)
{
    Console.WriteLine(p.Field<string>("Name"));
}

This example uses Select to return a sequence of only product names.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable products = ds.Tables["Product"];

IEnumerable<string> query =
    from product in products.AsEnumerable()
    select product.Field<string>("Name");

Console.WriteLine("Product Names:");
foreach (string productName in query)
{
    Console.WriteLine(productName);
}

This example uses From …, … (the equivalent of the SelectMany method) to select all orders where TotalDue is less than 500.00.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable contacts = ds.Tables["Contact"];
DataTable orders = ds.Tables["SalesOrderHeader"];

var query =
    from contact in contacts.AsEnumerable()
    from order in orders.AsEnumerable()
    where contact.Field<int>("ContactID") == order.Field<int>("ContactID")
        && order.Field<decimal>("TotalDue") < 500.00M
    select new
    {
        ContactID = contact.Field<int>("ContactID"),
        LastName = contact.Field<string>("LastName"),
        FirstName = contact.Field<string>("FirstName"),
        OrderID = order.Field<int>("SalesOrderID"),
        Total = order.Field<decimal>("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);
}

This example uses From …, … (the equivalent of the SelectMany method) to select all orders where the order was made on October 1, 2002 or later.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable contacts = ds.Tables["Contact"];
DataTable orders = ds.Tables["SalesOrderHeader"];

var query =
    from contact in contacts.AsEnumerable()
    from order in orders.AsEnumerable()
    where contact.Field<int>("ContactID") == order.Field<int>("ContactID") &&
        order.Field<DateTime>("OrderDate") >= new DateTime(2002, 10, 1)
    select new
    {
        ContactID = contact.Field<int>("ContactID"),
        LastName = contact.Field<string>("LastName"),
        FirstName = contact.Field<string>("FirstName"),
        OrderID = order.Field<int>("SalesOrderID"),
        OrderDate = order.Field<DateTime>("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);
}

This example uses a 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.

            // Fill the DataSet.
            DataSet ds = new DataSet();
            ds.Locale = CultureInfo.InvariantCulture;
            FillDataSet(ds);

            DataTable contacts = ds.Tables["Contact"];
            DataTable orders = ds.Tables["SalesOrderHeader"];

            var query =
                from contact in contacts.AsEnumerable()
                from order in orders.AsEnumerable()
                let total = order.Field<decimal>("TotalDue")
                where contact.Field<int>("ContactID") == order.Field<int>("ContactID") &&
                      total >= 10000.0M
                select new
                {
                    ContactID = contact.Field<int>("ContactID"),
                    LastName = contact.Field<string>("LastName"),
                    OrderID = order.Field<int>("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);
            }
Show:
© 2015 Microsoft