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: Restriction (LINQ to DataSet)

The examples in this topic demonstrate how to use the Where method 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 returns all online orders.

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

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

var query =
    from order in orders.AsEnumerable()
    where order.Field<bool>("OnlineOrderFlag") == true 
    select new
    {
        SalesOrderID = order.Field<int>("SalesOrderID"),
        OrderDate = order.Field<DateTime>("OrderDate"),
        SalesOrderNumber = order.Field<string>("SalesOrderNumber")
    };

foreach (var onlineOrder in query)
{
    Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}",
        onlineOrder.SalesOrderID,
        onlineOrder.OrderDate,
        onlineOrder.SalesOrderNumber);
}

This example returns the orders where the order quantity is greater than 2 and less than 6.

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

DataTable orders = ds.Tables["SalesOrderDetail"];

var query =
    from order in orders.AsEnumerable()
    where order.Field<Int16>("OrderQty") > 2 &&
        order.Field<Int16>("OrderQty") < 6
    select new
    {
        SalesOrderID = (int)order.Field<int>("SalesOrderID"),
        OrderQty = order.Field<Int16>("OrderQty")
    };

foreach (var order in query)
{
    Console.WriteLine("Order ID: {0} Order quantity: {1}",
        order.SalesOrderID, order.OrderQty);
}

This example returns all red colored products.

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

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

var query =
    from product in products.AsEnumerable()
    where product.Field<string>("Color") == "Red" 
    select new
    {
        Name = product.Field<string>("Name"),
        ProductNumber = product.Field<string>("ProductNumber"),
        ListPrice = product.Field<Decimal>("ListPrice")
    };

foreach (var product in query)
{
    Console.WriteLine("Name: {0}", product.Name);
    Console.WriteLine("Product number: {0}", product.ProductNumber);
    Console.WriteLine("List price: ${0}", product.ListPrice);
    Console.WriteLine("");
}

This example uses the Where method to find orders that were made after December 1, 2002 and then uses the GetChildRows method to get the details for each order.

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

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

IEnumerable<DataRow> query =
    from order in orders.AsEnumerable()
    where order.Field<DateTime>("OrderDate") >= new DateTime(2002, 12, 1)
    select order;


Console.WriteLine("Orders that were made after 12/1/2002:");
foreach (DataRow order in query)
{
    Console.WriteLine("OrderID {0} Order date: {1:d} ",
        order.Field<int>("SalesOrderID"), order.Field<DateTime>("OrderDate"));
    foreach (DataRow orderDetail in order.GetChildRows("SalesOrderHeaderDetail"))
    {
        Console.WriteLine("  Product ID: {0} Unit Price {1}",
            orderDetail["ProductID"], orderDetail["UnitPrice"]);
    }
}
Show:
© 2015 Microsoft