Query examples

A catalogue of common queries against the Northwind model. The details are covered in Where clauses, Ordering, paging and expand and Projections.

Setup

Every example assumes Breeze's default configuration, as in Getting started, so property names are camelCase, and this manager:

import { EntityManager, EntityQuery, FilterQueryOp, Predicate } from 'breeze-client';
import { Category, Customer, Employee, Order, Product } from './model';   // your generated classes

const em = new EntityManager('breeze/NorthwindIBModel');

Basic queries

// Three equivalent queries for all customers
const q1 = EntityQuery.from(Customer);
const q2 = new EntityQuery('Customers');
const q3 = new EntityQuery().from('Customers');

try {
  const { results } = await em.executeQuery(q1);
  console.log(`${results.length} customers`);
} catch (err) {
  console.error(err.message);
}

Filtering

Each of these can also be written as an object. The two forms build the identical query - see the two forms side by side - so the object form is shown alongside wherever it reads differently.

Simple conditions

// Customers whose names start with "A"
EntityQuery.from(Customer)
  .where('companyName', 'startsWith', 'A');

// ...the same, with the FilterQueryOp enum
EntityQuery.from(Customer)
  .where('companyName', FilterQueryOp.StartsWith, 'A');

// ...or as an object
EntityQuery.from(Customer).where({ companyName: { startsWith: 'A' } });

// Orders with freight over $100
EntityQuery.from(Order)
  .where('freight', '>', 100);

// ...the same, with the FilterQueryOp enum
EntityQuery.from(Order)
  .where('freight', FilterQueryOp.GreaterThan, 100);

// ...or as an object
EntityQuery.from(Order).where({ freight: { gt: 100 } });

// Orders placed after February 1, 1998 (JavaScript months start at 0)
EntityQuery.from(Order)
  .where('orderDate', '>', new Date(1998, 1, 1));

// Orders that have not shipped
EntityQuery.from(Order)
  .where('shippedDate', '==', null);

// Orders shipped after they were due: compares two properties of the same order
EntityQuery.from(Order)
  .where('shippedDate', '>', 'requiredDate');

// Customers whose name contains "market"
EntityQuery.from(Customer)
  .where('companyName', FilterQueryOp.Contains, 'market');

// Customers in either of two countries
EntityQuery.from(Customer)
  .where('country', 'in', ['Belgium', 'Germany']);

Compound conditions with predicates

const baseQuery = EntityQuery.from(Order);

// Freight over $100 AND ordered after April 1, 1998
const p1 = Predicate.create<Order>('freight', '>', 100);
const p2 = Predicate.create<Order>('orderDate', '>', new Date(1998, 3, 1));
baseQuery.where(p1.and(p2));

// ...AND them with the static method, passing an array
baseQuery.where(Predicate.and([p1, p2]));

// ...or fluently
const po = Predicate.for(Order);
baseQuery.where(
  po('freight', '>', 100)
    .and(po('orderDate', '>', new Date(1998, 3, 1)))
);

// Freight over $100 OR ordered after April 1, 1998
baseQuery.where(
  po('freight', '>', 100)
    .or(po('orderDate', '>', new Date(1998, 3, 1)))
);

// Composition runs left to right:
// (on or after Jan 1 1996 OR before Jan 1 1997) AND freight over $100
const pred = po('orderDate', '>=', new Date(Date.UTC(1996, 0, 1)))
  .or(po('orderDate', '<', new Date(Date.UTC(1997, 0, 1))))
  .and(po('freight', '>', 100));
baseQuery.where(pred);

// Negation: freight NOT over $100
const big = Predicate.create<Order>('freight', '>', 100);
baseQuery.where(big.not());
baseQuery.where(Predicate.not(big));   // the same

Date.UTC gives an unambiguous UTC date with no time component.

To see what a predicate becomes, serialize it:

console.log(JSON.stringify(pred.toJSON()));

{"and":[{"or":[{"orderDate":{"ge":"1996-01-01T00:00:00.000Z"}},{"orderDate":{"lt":"1997-01-01T00:00:00.000Z"}}]},{"freight":{"gt":100}}]}

Conditions on related properties

// Products in a category whose name starts with "S"
EntityQuery.from(Product)
  .where('category.categoryName', 'startsWith', 'S');

// Orders sold to a customer in California
EntityQuery.from(Order)
  .where('customer.region', '==', 'CA');

Any and all

// Employees with any order where freight > 950
EntityQuery.from(Employee)
  .where('orders', 'any', 'freight', '>', 950);

// ...with FilterQueryOp values
EntityQuery.from(Employee)
  .where('orders', FilterQueryOp.Any, 'freight', FilterQueryOp.GreaterThan, 950);

// ...with the 'some' alias
EntityQuery.from(Employee)
  .where('orders', 'some', 'freight', '>', 950);

// ...built in pieces
const bigFreight = Predicate.create<Order>('freight', '>', 950);
EntityQuery.from(Employee)
  .where('orders', FilterQueryOp.Any, bigFreight);

// Customers with no orders
const hasOrders = Predicate.create<Customer>('orders', 'any', 'orderID', '!=', null);
EntityQuery.from(Customer)
  .where(hasOrders.not());

// Employees with an order for a customer whose name starts with "Lazy"
EntityQuery.from(Employee)
  .where('orders', 'any', 'customer.companyName', 'startsWith', 'Lazy')
  .expand('orders.customer');

// Across a many-to-many relationship: Orders -> OrderDetails -> Product
EntityQuery.from(Order)
  .where('orderDetails', 'any', 'product.productName', '==', 'Chai')
  .expand('orderDetails.product');

// A compound inner condition
const po2 = Predicate.for(Order);
const p = po2('freight', '>', 950).and(po2('shipCountry', 'startsWith', 'G'));
EntityQuery.from(Employee)
  .where('orders', 'any', p)
  .expand('orders');

// Nested: customers with an order where every line has unit price > $200
EntityQuery.from(Customer)
  .where('orders', 'any', 'orderDetails', 'all', 'unitPrice', '>', 200);

Functions

// Company name starts with "C" or "c"
EntityQuery.from(Customer)
  .where('toLower(companyName)', 'startsWith', 'c');

// 2nd and 3rd letters are "OM"
EntityQuery.from(Customer)
  .where('toUpper(substring(companyName, 1, 2))', '==', 'OM');

Not every server supports every function. See Functions for the list the Breeze .NET server understands.

Where clauses as JSON

// Customers whose names start with "A"
EntityQuery.from(Customer)
  .where({ companyName: { startsWith: 'A' } });

// Customers in Berlin, Germany (equals is the default; properties are ANDed)
EntityQuery.from(Customer)
  .where({ country: 'Germany', city: 'Berlin' });

// Employees hired before 1993, or with a D surname in the USA
EntityQuery.from(Employee).where({
  or: [
    { hireDate: { lt: new Date(1993, 0, 1) } },
    { and: [{ lastName: { startsWith: 'D' } }, { country: 'USA' }] },
  ],
});

The full syntax is in The object form in full.

Sorting

By one property

// Products by name, ascending
EntityQuery.from(Product)
  .orderBy('productName');

// ...descending
EntityQuery.from(Product)
  .orderBy('productName desc');

// ...descending, another way
EntityQuery.from(Product)
  .orderByDesc('productName');

By several properties

// Highest price first, then by name
EntityQuery.from(Product)
  .orderBy('unitPrice desc, productName');

By related properties

// Products by category name, descending
EntityQuery.from(Product)
  .orderBy('category.categoryName desc');

// Products by category name, then by product name descending
EntityQuery.from(Product)
  .orderBy('category.categoryName, productName desc');

Paging

// The first 5 products
EntityQuery.from(Product)
  .take(5);

// The first 5 products starting with "C", plus the total that start with "C"
const query = EntityQuery.from(Product)
  .where('productName', 'startsWith', 'C')
  .orderBy('productName')
  .take(5)
  .inlineCount();

const { results, inlineCount } = await em.executeQuery(query);
const pages = Math.ceil(inlineCount / 5);

// Skip the first 10 products and return the rest
EntityQuery.from(Product)
  .orderBy('productName')
  .skip(10);

// The 3rd page of 5 products
EntityQuery.from(Product)
  .orderBy('productName')
  .skip(10)
  .take(5);

// The first 10 products after sorting by category name, descending
EntityQuery.from(Product)
  .orderBy('category.categoryName desc')
  .take(10);

Always sort when you page. Without an orderBy, the server's row order is not guaranteed, and pages can overlap or skip rows.

Projections

select returns plain objects with just the properties you ask for. See Projections.

// Just the names of customers starting with "C"
EntityQuery.from(Customer)
  .where('companyName', 'startsWith', 'C')
  .select('companyName');

// The orders of customers starting with "C"
EntityQuery.from(Customer)
  .where('companyName', 'startsWith', 'C')
  .select('orders');

// Several properties
EntityQuery.from(Customer)
  .where('companyName', FilterQueryOp.StartsWith, 'C')
  .select('customerID, companyName, contactName')
  .orderBy('companyName');

// A related property: names of customers with orders over $500 freight
EntityQuery.from(Order)
  .where('freight', FilterQueryOp.GreaterThan, 500)
  .select('customer.companyName')
  .orderBy('customer.companyName');

Eager loading with expand

One relation

// Products in categories starting with "S", with each product's Category
EntityQuery.from(Product)
  .where('category.categoryName', 'startsWith', 'S')
  .expand('category');

Several relations

// The first 20 orders, with their Customer and their OrderDetails
EntityQuery.from(Order)
  .take(20)
  .expand('customer, orderDetails');

A property path

// The first 20 orders, with their OrderDetails and each detail's Product
EntityQuery.from(Order)
  .take(20)
  .expand('orderDetails.product');

One entity by key, expanded

// Order 10248 with its details: like fetchEntityByKey (below), but expanded
EntityQuery.from(Order)
  .where('orderID', '==', 10248)
  .expand('orderDetails');

By key

fetchEntityByKey fetches one entity from the server by its key. The result also tells you whether it came from the cache:

const { entity, fromCache } = await em.fetchEntityByKey('Employee', 1);

Pass true as the last argument to look in the cache first, and query the server only if the entity isn't there:

const { entity } = await em.fetchEntityByKey('Employee', 1, true);

getEntityByKey looks only in the cache. It never calls the server, so it is not really a query. It returns the entity or null immediately:

const employee = em.getEntityByKey('Employee', 1);

To use a key in a query you can extend, for example with expand, use fromEntityKey:

const key = employee.entityAspect.getKey();
const query = EntityQuery.fromEntityKey(key).expand('orders');

Related entities on demand

Load a navigation property you did not expand:

// Through the entity
await employee.entityAspect.loadNavigationProperty('orders');

// ...or build the query yourself, to add conditions
const query = EntityQuery.fromEntityNavigation(employee, 'orders')
  .where('freight', '>', 100);
const { results } = await em.executeQuery(query);

See Navigation properties.

Refreshing entities you already have

fromEntities builds a query for fresh copies of entities, all of the same type. By default, incoming values don't overwrite unsaved changes. Use OverwriteChanges to force a refresh:

import { MergeStrategy } from 'breeze-client';

const query = EntityQuery.fromEntities(customers)
  .using(MergeStrategy.OverwriteChanges);

await em.executeQuery(query);

A bag of lookups

A query can return an object whose properties hold lists of entities. This is a good way to fill the cache with lookup lists in one call. On the server:

[HttpGet]
public object Lookups() {
  var regions = PersistenceManager.Context.Regions;
  var territories = PersistenceManager.Context.Territories;
  var categories = PersistenceManager.Context.Categories;

  return new { regions, territories, categories };
}

On the client:

await EntityQuery.from('Lookups').using(em).execute();

// The Region, Territory and Category entities are in the cache now
const categories = em.executeQueryLocally(EntityQuery.from(Category));