Up LINQ query examples

Restriction operators

Last modified on August 15, 2012 17:21

Examples of the LINQ Where, Any and All operators are shown below.  In the examples below _em1 is an EntityManager.

C#
[Description("This sample uses WHERE to find all customers whose contact title is Sales Representative.")]
public void LinqToEntities01() {

  var query = from cust in _em1.Customers
              where cust.ContactTitle == "Sales Representative"
              orderby cust.CompanyName
              select cust;
  var r = query.ToList();
  Assert.IsTrue(r.First().CompanyName == "Alfreds Futterkiste");
}

[Description("This sample uses WHERE to find all orders placed before 1997.")]
public void LinqToEntities02() {
  var metadata = EntityMetadataStore.Instance.GetEntityMetadata(typeof(InternationalOrder));
  DateTime dt = new DateTime(1997, 1, 1);
  var query = from order in _em1.Orders
              where order.OrderDate < dt
              orderby order.OrderDate
              select order;
  Assert.IsTrue(query.First().Freight == 32.38M);
}

[Description("This sample uses WHERE to filter for Products that have stock below their reorder level and have a units on]
public void LinqToEntities03() {
  var query = from p in _em1.Products
              where p.UnitsInStock < p.ReorderLevel && p.UnitsOnOrder == 0
              orderby p.UnitsInStock
              select p;
  Assert.IsTrue(query.First().ProductName == "Nord-Ost Matjeshering");
}

[Description("This sample uses WHERE to filter out Products that have a UnitPrice less than 10.")]
public void LinqToEntities04() {
  var query = from p in _em1.Products
              where p.UnitPrice < 10
              orderby p.ProductName
              select p;
  Assert.IsTrue(query.First().ProductName == "Filo Mix");
}

[Description("This sample uses WHERE to find Employees in London.")]
public void LinqToEntities04a() {
  var query = from e in _em1.Employees
              where e.Address.City == "London"
              orderby e.EmployeeID
              select e;
  Assert.IsTrue(query.First().LastName == "Buchanan");
}

[Description("This sample uses WHERE to get previous employees.")]
public void LinqToEntities05() {
  var query = from e in _em1.Employees
              where e is PreviousEmployee
              orderby e.EmployeeID
              select e;
  var r = query.ToList();
  Assert.IsTrue(r.First().LastName == "King");
}

[Description("This sample uses WHERE to get employees who handle the Boston territory.")]
public void LinqToEntities06() {

  var query = from e in _em1.Employees.OfType<CurrentEmployee>()
              where e.Territories.Any(t => t.TerritoryDescription == "Boston")
              orderby e.EmployeeID
              select e;
  Assert.IsTrue(query.First().LastName == "Fuller");
}

[Description("This sample uses any Customers who placed an order in 1997.")]
public void LinqToEntities07() {
  var query = from c in _em1.Customers
              where c.Orders.Any(o => o.OrderDate.HasValue == true &&
                  o.OrderDate.Value.Year == 1997)
              select c;
  Assert.IsTrue(query.Count() == 85);
}

[Description("This sample uses ANY to check for any out-of-stock products.")]
public void LinqToEntities08() {
  var query = _em1
      .Suppliers
      .Where(s => s.Products
                 .Any(p => p.UnitsInStock == 0))
      .Select(s => s);
  Assert.IsTrue(query.Count() == 5);
}

[Description("This sample uses WHERE and ANY to get orders containing a product with a unit on order.")]
public void LinqToEntities09() {
  var query = from o in _em1.Orders
              where o.OrderDetails.Any(od => od.Product.UnitsOnOrder > 0)
              select o;
  Assert.IsTrue(query.ToList().Count == 366);
  Assert.IsTrue(query.Count() == 366);
}

[Description("This sample uses COUNT to get Products sold to Customers in the same Country " +
    "as the Products' Suppliers, and where all the Products in the order were from the same Country.")]
public void LinqToEntities10() {
  var query = from p in _em1.Products
              where p.OrderDetails.Count(od => od.Order.Customer.Address.Country ==
                  p.Supplier.Address.Country) > 2
              select p;
  Assert.IsTrue(query.Count() == 20);
}
VB

<Description("This sample uses WHERE to find all customers whose contact title is Sales Representative.")>
Public Sub LinqToEntities01()

 Dim query = From cust In _em1.Customers
              Where cust.ContactTitle = "Sales Representative"
              Order By cust.CompanyName
             Select cust
 Dim r = query.ToList()
  Assert.IsTrue(r.First().CompanyName = "Alfreds Futterkiste")
End Sub

<Description("This sample uses WHERE to find all orders placed before 1997.")>
Public Sub LinqToEntities02()
 Dim metadata = EntityMetadataStore.Instance.GetEntityMetadata(GetType(InternationalOrder))
 Dim dt As New Date(1997, 1, 1)
 Dim query = From order In _em1.Orders
              Where order.OrderDate < dt
              Order By order.OrderDate
             Select order
  Assert.IsTrue(query.First().Freight = 32.38D)
End Sub

<Description("This sample uses WHERE to filter for Products that have stock below their reorder level and have a units on>
Public Sub LinqToEntities03()
 Dim query = From p In _em1.Products
              Where p.UnitsInStock < p.ReorderLevel AndAlso p.UnitsOnOrder = 0
              Order By p.UnitsInStock
             Select p
  Assert.IsTrue(query.First().ProductName = "Nord-Ost Matjeshering")
End Sub

<Description("This sample uses WHERE to filter out Products that have a UnitPrice less than 10.")>
Public Sub LinqToEntities04()
 Dim query = From p In _em1.Products
              Where p.UnitPrice < 10
              Order By p.ProductName
             Select p
  Assert.IsTrue(query.First().ProductName = "Filo Mix")
End Sub

<Description("This sample uses WHERE to find Employees in London.")>
Public Sub LinqToEntities04a()
 Dim query = From e In _em1.Employees
              Where e.Address.City = "London"
              Order By e.EmployeeID
             Select e
  Assert.IsTrue(query.First().LastName = "Buchanan")
End Sub

<Description("This sample uses WHERE to get previous employees.")>
Public Sub LinqToEntities05()
 Dim query = From e In _em1.Employees
              Where TypeOf e Is PreviousEmployee
              Order By e.EmployeeID
             Select e
 Dim r = query.ToList()
  Assert.IsTrue(r.First().LastName = "King")
End Sub

<Description("This sample uses WHERE to get employees who handle the Boston territory.")>
Public Sub LinqToEntities06()

 Dim query = From e In _em1.Employees.OfType(Of CurrentEmployee)()
              Where e.Territories.Any(Function(t) t.TerritoryDescription = "Boston")
              Order By e.EmployeeID
             Select e
  Assert.IsTrue(query.First().LastName = "Fuller")
End Sub

<Description("This sample uses any Customers who placed an order in 1997.")>
Public Sub LinqToEntities07()
 Dim query = From c In _em1.Customers
              Where c.Orders.Any(Function(o) o.OrderDate.HasValue = True AndAlso o.OrderDate.Value.Year = 1997)
             Select c
  Assert.IsTrue(query.Count() = 85)
End Sub

<Description("This sample uses ANY to check for any out-of-stock products.")>
Public Sub LinqToEntities08()
 Dim query = _em1.Suppliers.Where(Function(s) s.Products.Any(Function(p) p.UnitsInStock = 0)).Select(Function(s) s)
  Assert.IsTrue(query.Count() = 5)
End Sub

<Description("This sample uses WHERE and ANY to get orders containing a product with a unit on order.")>
Public Sub LinqToEntities09()
 Dim query = From o In _em1.Orders
              Where o.OrderDetails.Any(Function(od) od.Product.UnitsOnOrder > 0)
             Select o
  Assert.IsTrue(query.ToList().Count = 366)
  Assert.IsTrue(query.Count() = 366)
End Sub

<Description("This sample uses COUNT to get Products sold to Customers in the same Country " & "as the Products' Suppliers, and where all the Products in the order were from the same Country.")>
Public Sub LinqToEntities10()
 Dim query = From p In _em1.Products
              Where p.OrderDetails.Count(Function(od) od.Order.Customer.Address.Country = p.Supplier.Address.Country) > 2
             Select p
  Assert.IsTrue(query.Count() = 20)
End Sub
Tags: Query
Created by DevForce on February 18, 2011 03:36

This wiki is licensed under a Creative Commons 2.0 license. XWiki Enterprise 3.2 - Documentation. Copyright © 2020 IdeaBlade