Entity SQL Reference

 

This section contains Entity SQL reference topics. This topic summarizes and groups the Entity SQL operators by category.

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

OperatorUse
+ (Add)Addition.
"/ (Divide)"Division.
% (Modulo)Returns the remainder of a division.
* (Multiply)Multiplication.
- (Negative)Negation.
- (Subtract)Subtraction.

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

FunctionType
Aggregate Entity SQL Canonical FunctionsDiscusses aggregate Entity SQL canonical functions.
Math Canonical FunctionsDiscusses math Entity SQL canonical functions.
String Canonical FunctionsDiscusses string Entity SQL canonical functions.
Date and Time Canonical FunctionsDiscusses date and time Entity SQL canonical functions.
Bitwise Canonical FunctionsDiscusses bitwise Entity SQL canonical functions.
Other Canonical FunctionsDiscusses functions not classified as bitwise, date/time, string, math, or aggregate.

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.

OperatorDescription
= (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] NULLDetermines 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] BETWEENDetermines 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] LIKEDetermines whether a specific character string matches a specified pattern.

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.

OperatorDescription
&& (Logical AND)Logical AND.
! (Logical NOT)Logical NOT.
|| (Logical OR)Logical OR.
CASEEvaluates a set of Boolean expressions to determine the result.
THENThe result of a WHEN clause when it evaluates to true.

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

OperatorUse
FROMSpecifies the collection that is used in SELECT statements.
GROUP BYSpecifies groups into which objects that are returned by a query (SELECT) expression are to be placed.
GroupPartitionReturns a collection of argument values, projected off the group partition to which the aggregate is related.
HAVINGSpecifies a search condition for a group or an aggregate.
LIMITUsed with the ORDER BY clause to performed physical paging.
ORDER BYSpecifies the sort order that is used on objects returned in a SELECT statement.
SELECTSpecifies the elements in the projection that are returned by a query.
SKIPUsed with the ORDER BY clause to performed physical paging.
TOPSpecifies that only the first set of rows will be returned from the query result.
WHEREConditionally filters data that is returned by a query.

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.

OperatorUse
CREATEREFCreates references to an entity in an entity set.
DEREFDereferences a reference value and produces the result of that dereference.
KEYExtracts the key of a reference or of an entity expression.
NAVIGATEAllows you to navigate over the relationship from one entity type to another
REFReturns a reference to an entity instance.

Entity SQL provides various powerful set operations. This includes set operators similar to Transact-SQL operators 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.

OperatorUse
ANYELEMENTExtracts an element from a multivalued collection.
EXCEPTReturns 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] EXISTSDetermines if a collection is empty.
FLATTENConverts a collection of collections into a flattened collection.
[NOT] INDetermines whether a value matches any value in a collection.
INTERSECTReturns a collection of any distinct values that are returned by both the query expressions on the left and right sides of the INTERSECT operand.
OVERLAPSDetermines whether two collections have common elements.
SETUsed to convert a collection of objects into a set by yielding a new collection with all duplicate elements removed.
UNIONCombines the results of two or more queries into a single collection.

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

OperatorUse
CASTConverts an expression of one data type to another.
COLLECTIONUsed in a FUNCTION operation to declare a collection of entity types or complex types.
IS [NOT] OFDetermines whether the type of an expression is of the specified type or one of its subtypes.
OFTYPEReturns a collection of objects from a query expression that is of a specific type.
Named Type ConstructorUsed to create instances of entity types or complex types.
MULTISETCreates an instance of a multiset from a list of values.
ROWConstructs anonymous, structurally typed records from one or more values.
TREATTreats an object of a particular base type as an object of the specified derived type.

The following table lists other Entity SQL operators.

OperatorUse
+ (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 conceptual model type.
-- (Comment)Include Entity SQL comments.
FUNCTIONDefines an inline function that can be executed in an Entity SQL query.

Entity SQL Language

Show: