Query Expression Syntax Examples: Restriction (LINQ to Entities)
The examples in this topic demonstrate how to use the Where method to query the AdventureWorks Sales Model using query expression syntax. 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 L2EExamplesVB.AdventureWorksModel
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 AdventureWorksModel;
using System.Globalization;
For more information, see How to: Create a LINQ to Entities Project in Visual Studio.
Where
Example
The following example returns all online orders.
Using AWEntities As New AdventureWorksEntities
Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader
Dim onlineOrders = _
From order In orders _
Where order.OnlineOrderFlag = True _
Select 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 AWEntities = new AdventureWorksEntities())
{
ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;
var onlineOrders =
from order in orders
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.
Using AWEntities As New AdventureWorksEntities
Dim orders As ObjectQuery(Of SalesOrderDetail) = AWEntities.SalesOrderDetail
Dim query = _
From order In orders _
Where order.OrderQty > 2 And order.OrderQty < 6 _
Select 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
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
ObjectQuery<SalesOrderDetail> orders = AWEntities.SalesOrderDetail;
var query =
from order in orders
where order.OrderQty > 2 && order.OrderQty < 6
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.
Using AWEntities As New AdventureWorksEntities
Dim products As ObjectQuery(Of Product) = AWEntities.Product
Dim query = _
From product In products _
Where product.Color = "Red" _
Select 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
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
ObjectQuery<Product> products = AWEntities.Product;
var query =
from product in products
where product.Color == "Red"
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 AWEntities As New AdventureWorksEntities
Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader
Dim query = _
From order In orders _
Where order.OrderDate >= New DateTime(2003, 12, 1) _
Select 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.SalesOrderDetail
Console.WriteLine(" Product ID: {0} Unit Price {1}", _
orderDetail.ProductID, orderDetail.UnitPrice)
Next
Next
End Using
using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;
IQueryable<SalesOrderHeader> query =
from order in orders
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.SalesOrderDetail)
{
Console.WriteLine(" Product ID: {0} Unit Price {1}",
orderDetail.ProductID, orderDetail.UnitPrice);
}
}
}