Entity SQL Reference

This section contains Entity SQL reference topics. In this topic, Entity SQL operators are summarized and grouped by category.

Arithmetic Operators

Arithmetic operators perform mathematical operations on two expressions of one or more numeric data types. The following table lists the Entity SQL arithmetic operators.

Operator Use

+ (Add)

Addition.

/ (Divide)

Division.

% (Modulo)

Returns the remainder of a division.

* (Multiply)

Multiplication.

- (Negative)

Negation.

- (Subtract)

Subtraction.

Canonical Functions

Canonical functions are supported by all data providers, and can be used by all querying technologies. The following table lists the canonical functions.

Function Type

Aggregate Entity SQL Canonical Functions

Discusses aggregate Entity SQL canonical functions.

Math Canonical Functions

Discusses math Entity SQL canonical functions.

String Canonical Functions

Discusses string Entity SQL canonical functions.

Date and Time Canonical Functions

Discusses date and time Entity SQL canonical functions.

Bitwise Canonical Functions

Discusses bitwise Entity SQL canonical functions.

Other Canonical Functions

Discusses functions not classified as bitwise, date/time, string, math, or aggregate.

Comparison Operators

Comparison operators are defined for the following types: Byte, Int16, Int32, Int64, Double, Single, Decimal, String, DateTime, Date, Time, DateTimeOffset. Implicit type promotion occurs for the operands before the comparison operator is applied. Comparison operators always yield Boolean values. When at least one of the operands is null, the result is null.

Equality and inequality are defined for any object type that has identity, such as the Boolean type. Non-primitive objects with identity are considered equal if they share the same identity. The following table lists the Entity SQL comparison operators.

Operator Description

= (Equals)

Compares the equality of two expressions.

> (Greater Than)

Compares two expressions to determine whether the left expression has a value greater than the right expression.

>= (Greater Than or Equal To)

Compares two expressions to determine whether the left expression has a value greater than or equal to the right expression.

IS [NOT] NULL

Determines if a query expression is null.

< (Less Than)

Compares two expressions to determine whether the left expression has a value less than the right expression.

<= (Less Than or Equal To)

Compares two expressions to determine whether the left expression has a value less than or equal to the right expression.

[NOT] BETWEEN

Determines whether an expression results in a value in a specified range.

!= (Not Equal To)

Compares two expressions to determine whether the left expression is not equal to the right expression.

<> (Not Equal To)

Compares two expressions to determine whether the left expression is not equal to the right expression.

[NOT] LIKE

Determines whether a specific character string matches a specified pattern.

Logical and Case Expression Operators

Logical operators test for the truth of a condition. The CASE expression evaluates a set of Boolean expressions to determine the result. The following table lists the logical and CASE expression operators.

Operator Description

&& (Logical AND)

Logical AND.

! (Logical NOT)

Logical NOT.

|| (Logical OR)

Logical OR.

CASE

Evaluates a set of Boolean expressions to determine the result.

ELSE

Imposes conditions on the execution of an Entity SQL statement.

THEN

The result of a WHEN clause when it evaluates to true.

WHEN

Evaluates one or more Boolean expressions.

Query Operators

Query operators are used to define query expressions that return entity data. The following table lists query operators.

Operator Use

FROM

Specifies the collection used in SELECT statements.

GROUP BY

Specifies groups into which objects returned by a query (SELECT) expression are to be placed.

HAVING

Specifies a search condition for a group or an aggregate.

LIMIT

Used with the ORDER BY clause to performed physical paging.

ORDER BY

Specifies the sort order used on objects returned in a SELECT statement.

SELECT

Specifies the elements in the projection returned by a query.

SKIP

Used with the ORDER BY clause to performed physical paging.

TOP

Specifies that only the first set of rows will be returned from the query result.

WHERE

Conditionally filters data returned by a query.

Reference Operators

A reference is a logical pointer (foreign key) to a specific entity in a specific entity set. Entity SQL supports the following operators to construct, deconstruct and navigate through references.

Operator Use

CREATEREF

Creates references to an entity in an entity set.

DEREF

Dereferences a reference value and produces the result of that dereference.

KEY

Extracts the key of a reference or of an entity expression.

NAVIGATE

Allows you to navigate over the relationship from one entity type to another

REF

Returns a reference to an entity instance.

Set Operators

Entity SQL provides various powerful set operations. This includes set operators similar to those in Transact-SQL such as UNION, INTERSECT, EXCEPT, and EXISTS. Entity SQL also supports operators for duplicate elimination (SET), membership testing (IN), and joins (JOIN). The following table lists the Entity SQL set operators.

Operator Use

ANYELEMENT

Extracts an element from a multivalued collection.

EXCEPT

Returns a collection of any distinct values from the query expression to the left of the EXCEPT operand that are not also returned from the query expression to the right of the EXCEPT operand.

[NOT] EXISTS

Determines if a collection is empty.

FLATTEN

Converts a collection of collections into a flattened collection.

[NOT] IN

Determines whether a value matches any value in a collection.

INTERSECT

Returns a collection of any distinct values that are returned by both the query expressions on the left and right sides of the INTERSECT operand.

OVERLAPS

Determines whether two collections have common elements.

SET

Used to convert a collection of objects into a set by yielding a new collection with all duplicate elements removed.

UNION

Combines the results of two or more queries into a single collection.

Type Operators

Entity SQL provides operations that allow the type of an expression (value) to be constructed, queried and manipulated. The following table lists operators used to work with types.

Operator Use

CAST

Converts an expression of one data type to another.

IS [NOT] OF

Determines whether the type of an expression is of the specified type or one of its subtypes.

OFTYPE

Returns a collection of objects from a query expression that is of a specific type.

Named Type Constructor

Used to create instances of entity types or complex types.

MULTISET

Creates an instance of a multiset from a list of values.

ROW

Constructs anonymous, structurally typed records from one or more values.

TREAT

Treats an object of a particular base type as an object of the specified derived type.

Other Operators

The following table lists other Entity SQL operators.

Operator Use

+ (String Concatenation)

Used to concatenate strings in Entity SQL.

. (Member Access)

Used to access the value of a property or field of an instance of structural EDM type.

-- (Comment)

Include Entity SQL comments.

See Also

Concepts

Entity SQL Language