1 out of 1 rated this helpful - Rate this topic

Query Expression Syntax Examples: Filtering

.NET Framework 4.5

The examples in this topic demonstrate how to use the Where and Where…Contains methods to query the AdventureWorks Sales Model using query expression syntax. Note, Where…Contains cannot be used as a part of a compiled query.

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;

Where

Example

The following example returns all online orders.


using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var onlineOrders =
from order in context.SalesOrderHeaders
where order.OnlineOrderFlag == true
select new
{
SalesOrderID = order.SalesOrderID,
OrderDate = order.OrderDate,
SalesOrderNumber = order.SalesOrderNumber
};

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

Example

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


int orderQtyMin = 2;
int orderQtyMax = 6;
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var query =
from order in context.SalesOrderDetails
where order.OrderQty > orderQtyMin && order.OrderQty < orderQtyMax
select new
{
SalesOrderID = order.SalesOrderID,
OrderQty = order.OrderQty
};

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

Example

The following example returns all red colored products.


String color = "Red";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var query =
from product in context.Products
where product.Color == color
select new
{
Name = product.Name,
ProductNumber = product.ProductNumber,
ListPrice = product.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("");
}
}

Example

The following example uses the Where method to find orders that were made after December 1, 2003, and then uses the order.SalesOrderDetail navigation property to get the details for each order.


using (AdventureWorksEntities context = new AdventureWorksEntities())
{
IQueryable<SalesOrderHeader> query =
from order in context.SalesOrderHeaders
where order.OrderDate >= new DateTime(2003, 12, 1)
select order;


Console.WriteLine("Orders that were made after December 1, 2003:");
foreach (SalesOrderHeader order in query)
{
Console.WriteLine("OrderID {0} Order date: {1:d} ",
order.SalesOrderID, order.OrderDate);
foreach (SalesOrderDetail orderDetail in order.SalesOrderDetails)
{
Console.WriteLine("  Product ID: {0} Unit Price {1}",
orderDetail.ProductID, orderDetail.UnitPrice);
}
}
}

Where…Contains

Example

The following example uses an array as part of a Where…Contains clause to find all products that have a ProductModelID that matches a value in the array.


using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
int?[] productModelIds = {19, 26, 118};
var products = from p in AWEntities.Products
where productModelIds.Contains(p.ProductModelID)
select p;
foreach (var product in products)
{
Console.WriteLine("{0}: {1}", product.ProductModelID, product.ProductID);
}
}

Note:
As part of the predicate in a Where…Contains clause, you can use an Array, a List, or a collection of any type that implements the IEnumerable interface. You can also declare and initialize a collection within a LINQ to Entities query. See the next example for more information.

Example

The following example declares and initializes arrays in a Where…Contains clause to find all products that have a ProductModelID or Size that match values in the arrays.


using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
var products = from p in AWEntities.Products
where (new int?[] { 19, 26, 18 }).Contains(p.ProductModelID) ||
(new string[] { "L", "XL" }).Contains(p.Size)
select p;
foreach (var product in products)
{
Console.WriteLine("{0}: {1}, {2}", product.ProductID,
product.ProductModelID,
product.Size);
}
}

Query Expression Syntax Examples: Filtering

Where

Example

Example

Example

Example

Where…Contains

Example

Example

See Also

Concepts