Method-Based Query Syntax Examples: Filtering
The examples in this topic demonstrate how to use the Where and Where…Contains methods to query the AdventureWorks Sales Model using method-based query 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:
Option Explicit On
Option Strict On
Imports System.Data.Objects
Imports System.Globalization
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 context As New AdventureWorksEntities
Dim onlineOrders = context.SalesOrderHeaders _
.Where(Function(order) order.OnlineOrderFlag = True) _
.Select(Function(order) New With { _
.SalesOrderID = order.SalesOrderID, _
.OrderDate = order.OrderDate, _
.SalesOrderNumber = order.SalesOrderNumber _
})
For Each onlineOrder In onlineOrders
Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}", _
onlineOrder.SalesOrderID, _
onlineOrder.OrderDate, _
onlineOrder.SalesOrderNumber)
Next
End Using
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var onlineOrders = context.SalesOrderHeaders
.Where(order => order.OnlineOrderFlag == true)
.Select(s => new { s.SalesOrderID, s.OrderDate, s.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.
Dim orderQtyMin = 2
Dim orderQtyMax = 6
Using context As New AdventureWorksEntities
Dim query = context.SalesOrderDetails _
.Where(Function(order) order.OrderQty > orderQtyMin And order.OrderQty < orderQtyMax) _
.Select(Function(order) New With { _
.SalesOrderID = order.SalesOrderID, _
.OrderQty = order.OrderQty _
})
For Each order In query
Console.WriteLine("Order ID: {0} Order quantity: {1}", _
order.SalesOrderID, order.OrderQty)
Next
End Using
int orderQtyMin = 2;
int orderQtyMax = 6;
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var query = context.SalesOrderDetails
.Where(order => order.OrderQty > orderQtyMin && order.OrderQty < orderQtyMax)
.Select(s => new { s.SalesOrderID, s.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.
Dim color = "Red"
Using context As New AdventureWorksEntities
Dim query = context.Products _
.Where(Function(product) product.Color = color) _
.Select(Function(product) New With { _
.Name = product.Name, _
.ProductNumber = product.ProductNumber, _
.ListPrice = product.ListPrice _
})
For Each 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("")
Next
End Using
String color = "Red";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
var query = context.Products
.Where(product => product.Color == color)
.Select(p => new { p.Name, p.ProductNumber, p.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 context As New AdventureWorksEntities
Dim query = context.SalesOrderHeaders _
.Where(Function(order) order.OrderDate >= New DateTime(2003, 12, 1)) _
.Select(Function(order) order)
Console.WriteLine("Orders that were made after December 1, 2003:")
For Each order In query
Console.WriteLine("OrderID {0} Order date: {1:d} ", _
order.SalesOrderID, order.OrderDate)
For Each orderDetail In order.SalesOrderDetails
Console.WriteLine(" Product ID: {0} Unit Price {1}", _
orderDetail.ProductID, orderDetail.UnitPrice)
Next
Next
End Using
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
IQueryable<SalesOrderHeader> query = context.SalesOrderHeaders
.Where(order => order.OrderDate >= new DateTime(2003, 12, 1));
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 AWEntities As New AdventureWorksEntities()
Dim productModelIds As System.Nullable(Of Integer)() = {19, 26, 118}
Dim products = AWEntities.Products. _
Where(Function(p) productModelIds.Contains(p.ProductModelID))
For Each product In products
Console.WriteLine("{0}: {1}", product.ProductModelID, product.ProductID)
Next
End Using
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
int?[] productModelIds = { 19, 26, 118 };
var products = AWEntities.Products.
Where(p => productModelIds.Contains(p.ProductModelID));
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 a Size that matches a value in the arrays.
Using AWEntities As New AdventureWorksEntities()
Dim products = AWEntities.Products. _
Where(Function(p) (New System.Nullable(Of Integer)() {19, 26, 18}). _
Contains(p.ProductModelID) _
OrElse _
(New String() {"L", "XL"}).Contains(p.Size))
For Each product In products
Console.WriteLine("{0}: {1}, {2}", product.ProductID, _
product.ProductModelID, _
product.Size)
Next
End Using
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
var products = AWEntities.Products.
Where(p => (new int?[] { 19, 26, 18 }).Contains(p.ProductModelID) ||
(new string[] { "L", "XL" }).Contains(p.Size));
foreach (var product in products)
{
Console.WriteLine("{0}: {1}, {2}", product.ProductID,
product.ProductModelID,
product.Size);
}
}