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:
© 2014 Microsoft