Expressions Filters Sorting and Paging

Filters, Sorting & Paging

Filter, sort and page a collection with the functions :where, :orderBy, :offset, :limit, :first and :last, and chain them in any order.

Filter: :where

:where(condition) keeps the items of a collection that match the condition. It is a function like the others, so it chains with them in any order.

Collection:where(Condition)

Changed in 5.0. The old filter Orders(Status = 'Done') and the indexer Orders[0 10 asc Date] are removed. See the migration guide.

Comparison Operators

Operator	Syntax	Example
Equal	=	Status = 'Active'
Not Equal	!=	Status != 'Archived'
Greater Than	>	Total > 100
Less Than	<	Total < 50
Greater or Equal	>=	Score >= 90
Less or Equal	<=	Score <= 80
Contains	contains	Name contains 'John'
Starts With	startswith	Email startswith 'admin'
Ends With	endswith	Email endswith '@example.com'

Logical Operators

Operator	Alternative Syntax	Example
AND	&&, and, ,	Status = 'Done' && Total > 100
OR	`

Filter Examples

# Simple filter
Orders:where(Status = 'Completed')
 
# AND conditions (all equivalent)
Orders:where(Status = 'Done' && Total > 100)
Orders:where(Status = 'Done' and Total > 100)
Orders:where(Status = 'Done', Total > 100)           # Comma equals AND
 
# OR conditions
Orders:where(Status = 'Pending' || Status = 'Waiting')
Orders:where(Status = 'Pending' or Status = 'Waiting')
 
# Complex conditions with parentheses
Orders:where((Status = 'Done' && Total > 100) || Priority = 'High')
 
# Function in the condition
Orders:where(Items:count > 5)                        # Orders with more than 5 items
Users:where(Name:lower = 'john')                     # Case-insensitive comparison

Sorting and Paging

Five functions sort a collection and pick items from it. They chain with :where, with each other and with projections, in any order.

Function	Syntax	LINQ	MongoDB
orderBy	`:orderBy(Key [asc	desc], ...)`	OrderBy / ThenBy
offset	:offset(n)	Skip(n)	$slice
limit	:limit(n)	Take(n)	$slice
first	:first	FirstOrDefault()	$first
last	:last	LastOrDefault()	$last

• orderBy takes several keys, Orders:orderBy(CreatedAt, CreatedBy desc). The default direction is asc, and the next keys order the items that are equal on the previous ones.
• offset and limit take a whole number, 0 or greater. Anything else is an error.
• first and last give null for an empty collection. They do not sort: write orderBy before them (EF Core rejects LastOrDefault on an unordered query).
• orderBy takes property names only, not functions or navigation.

Sorting and Paging Examples

# First item by OrderDate ascending
Orders:orderBy(OrderDate):first
 
# Last item by OrderDate descending
Orders:orderBy(OrderDate desc):last
 
# First 10 items sorted by OrderDate
Orders:orderBy(OrderDate):limit(10)
 
# Skip 5, take 20 items sorted by Total descending
Orders:orderBy(Total desc):offset(5):limit(20)
 
# Two sort keys
Orders:orderBy(CreatedAt, CreatedBy desc)
 
# Filter, sort and page, then project
Orders:where(Status = 'Done'):orderBy(Total desc):offset(5):limit(10).{Id, Total}
 
# A property of the first item
Orders:orderBy(Date):first.Total

Usage Example

public class UserOrdersResponse
{
    public string UserId { get; set; }
    public List<OrderDto> CompletedOrders { get; set; }
    public List<OrderDto> LargeOrders { get; set; }
    public List<OrderDto> LargeCompletedOrders { get; set; }
    public List<OrderDto> ProcessedOrders { get; set; }
    public List<OrderDto> PhoneOrders { get; set; }
    public OrderDto FirstOrder { get; set; }
    public List<OrderDto> Top5Orders { get; set; }
    public string LargestCompletedOrderNumber { get; set; }
}
 
public class UserOrdersResponseProfile : ProfileOf<UserOrdersResponse>
{
    protected override void Configure()
    {
        UseDistributedKey<UserDistributedKey>()
            .Of(x => x.UserId)
            .For(x => x.CompletedOrders, "Orders:where(Status = 'Done')")
            .For(x => x.LargeOrders, "Orders:where(Total > 100)")
            .For(x => x.LargeCompletedOrders, "Orders:where(Status = 'Done' && Total > 100)")
            .For(x => x.ProcessedOrders, "Orders:where(Status = 'Done' || Status = 'Shipped')")
            .For(x => x.PhoneOrders, "Orders:where(ProductName contains 'Phone')")
            .For(x => x.FirstOrder, "Orders:orderBy(OrderDate):first")
            .For(x => x.Top5Orders, "Orders:orderBy(Total desc):limit(5)")
            .For(x => x.LargestCompletedOrderNumber, "Orders:where(Status = 'Done'):orderBy(Total desc):first.OrderNumber");
    }
}

Important Notes

• Comma , in filter equals && (AND)
• With EF Core, these functions become LINQ (OrderBy, Skip, Take) that the provider translates; with MongoDB they use the aggregation operators of the table above
• If the collection is null or empty, :first and :last give null and the other functions give an empty list

---

Source: https://fxmapper.net/docs/expressions/array-mapping/ — the website is always the most up to date version.